INDEX and MATCH functions - too many arguments
 

Hi

I'm hoping someone can assist. I want to return the amount in the last column(amount) however I need to ensure the 3 criteria's have been met.

Thanks you in advance
Paul

=INDEX(O48:U51,MATCH(O48:O51,0),MATCH(L49,Q48:Q51, 0),Match(M49,S48:S51,0),Match(N48,O48:U48,0))

JSID Vac Claim Type Amount JSID JSID2 Vac Vac2 Claim Type Claim2 Amount
1 a 7w 1 6 a e 7w 8w 600
2 7 b f 13w 4w 1500
3 8 c g 26w 6w 2500

INDEX and MATCH functions - too many arguments
 

Hi

I'm hoping someone can assist. I want to return the amount in the
last column(amount) however I need to ensure the 3 criteria's have
been met.


Thanks you in advance
Paul

=INDEX(O48:U51,MATCH(O48:O51,0),MATCH(L49,Q48:Q51, 0),Match(M49,S48:S51,0),Match(N48,O48:U48,0))

JSID Vac Claim Type Amount JSID JSID2 Vac Vac2 Claim
Type Claim2 Amount
1 a 7w 1 6 a e 7w 8w 600
2 7 b f 13w 4w 1500
3 8 c g 26w 6w 2500

Conditional criteria is better implemented using IF() and the AND()
functions...

=IF(AND(REF1=X,REF2=Y,REF3=Z),REF4,"")

...where if all 3 conditions inside AND are met then the value in REF4
is returned, else the cell gets an empty string. If you need a value
then substitute "" with the desired value (like zero, for example).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion