If formula with index, match error

**toclare84** · 10-01-2010, 07:14 AM

Hi Guys,

Could some have a look at this formula for me and try and see why it isnt working. It should bring back a value from list 1 if it says CL/Spouse and from another list if it doesnt, but the cell is just returning #VALUE

=IF(B18="CL/SPOUSE",INDEX(CONPOINTS!B112:C161,MATCH(F18,CONPOINTS!A112:A161,0),MATCH(C18,CONPOINTS!B112:$C$112,0),INDEX(CONPOINTS!D113:E161,MATCH(F18,CONPOINTS!A112:A161,0),MATCH(C18,CONPOINTS!C112:D112,0))))

thanks everyone

**DonkeyOte** · 10-01-2010, 07:23 AM

Based on my interpretation of the formula you're missing a closing parenthesis after the first INDEX ... also your reference to the second range is seemingly missing a row (112 rather than 113 I think ?)

=IF(B18="CL/SPOUSE",INDEX(CONPOINTS!B112:C161,MATCH(F18,CONPOINTS!A112:A161,0),MATCH(C18,CONPOINTS!B112:$C$112,0)),INDEX(CONPOINTS!D112:E161,MATCH(F18,CONPOINTS!A112:A161,0),MATCH(C18,CONPOINTS!C112:D112,0)))

or written another way:

=INDEX(IF(B18="CL/SPOUSE",CONPOINTS!B112:C161,D112:E161),MATCH(F18,CONPOINTS!A112:A161,0),MATCH(C18,IF(B18="CL/SPOUSE",CONPOINTS!B112:C112,CONPOINTS!C112:D112),0))