route217

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

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

=index($a$2:$ae$17,match(a

$a$2:$ae$17 pivot table data range

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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)))

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

=if(isna(index($a$2:$ae$17

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)),"")

=IF(ISNA(INDEX($A$2:$AE$17

if you have 2007 or later you may simplify it to

=IFERROR(INDEX($A$2:$AE$17

ASKER

Thanks. ...for the feedback. ..

can you shed some light on https://www.experts-exchange.com/questions/28333198/extract-text-string-using-formula-excel-2007.html

can you shed some light on https://www.experts-exchange.com/questions/28333198/extract-text-string-using-formula-excel-2007.html

You will have a better response if you upload a sample file.