Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

index match #n/a error

Hi expert's excel 2007

trying to complete an index match off a pivot table...getting #n/a error

=index($a$2:$ae$17,match(a33, $a$33:$a$42,0),match($c$32:$j$32))

$a$2:$ae$17 pivot table data range
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

You have not provided the actual formula. The second match formula contains insufficient arguments and is not accepted by excel.

You will have a better response if you upload a sample file.
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of route217

ASKER

Ssaqib...worked it out....

How would I amend to handle #n/a error. ..


=if(isna(index($a$2:$ae$17,match(a33, $a$33:$a$42,0),match(c31,$c$32:$j$32)),"",index($a$2:$ae$17,match(a33, $a$33:$a$42,0),match($c$32:$j$32)))
That formula should be

=IF(ISNA(INDEX($A$2:$AE$17,MATCH(A33, $A$33:$A$42,0),MATCH(C31,$C$32:$J$32))),"",INDEX($A$2:$AE$17,MATCH(A33, $A$33:$A$42,0),MATCH(C31,$C$32:$J$32)))

if you have 2007 or later you may simplify it to

=IFERROR(INDEX($A$2:$AE$17,MATCH(A33,$A$33:$A$42,0),MATCH(C31,$C$32:$J$32)),"")