Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

index match #n/a error

Posted on 2014-01-08
5
Medium Priority
?
335 Views
Last Modified: 2014-01-08
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
0
Comment
Question by:route217
  • 3
  • 2
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39764731
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.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39764732
Is A33 blank?
0
 

Author Comment

by:route217
ID: 39764751
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)))
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39764820
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)),"")
0
 

Author Comment

by:route217
ID: 39764827
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question