# Index/Match formula with multiple criteria

1. ## Index/Match formula with multiple criteria

I am trying to Match 2 criteria and return a result based on those 2 criteria matching. Ex. match part #, AND PO#, and return the date. Here is how I have the formula setup now, which is returning an NA error;

INDEX('sheet1'!DATE,MATCH(B34,'sheet1'!PART_NO.),MATCH(R34,'sheet1'!PO,0))

B34=PART #
R34=PO#

The referenced workbook has named ranges, that's why I am not using column ranges here.

2. ## Re: Index/Match formula with multiple criteria

This link will give you the formula you need.
http://support.microsoft.com/kb/214142

http://www.techonthenet.com/excel/fo...x_function.php
http://office.microsoft.com/en-us/ex...005209138.aspx
http://www.excelhero.com/blog/2011/0...ing-index.html
http://www.contextures.com/xlfunctions03.html
http://www.mrexcel.com/articles/exce...ndex-match.php

3. ## Re: Index/Match formula with multiple criteria

Thanks, Whiz. I used the formula in the microsoft link, and still no luck. Here is my formula;
INDEX(I9:I12,MATCH(\$B\$2&\$C\$2,G9:G12&H9:H12,0))

It's weird, because when i hit the fx formula button, under "formula result" it shows the correct result, but in the cell it just displays #VALUE. I'll attach a capture of the result and a sample sheet.

4. ## Re: Index/Match formula with multiple criteria

You need to confirm the formula with CTRL+SHIFT+ENTER, not just ENTER.

5. ## Re: Index/Match formula with multiple criteria

Originally Posted by Whizbang
You need to confirm the formula with CTRL+SHIFT+ENTER, not just ENTER.
Thanks!! I did not see that section under the "method 2" formula so i assumed it did not apply. Works like a charm!

6. ## Re: Index/Match formula with multiple criteria

Originally Posted by Whizbang
You need to confirm the formula with CTRL+SHIFT+ENTER, not just ENTER.
Is there anyway I could combine that with an IF statement so it returns a blank instead of NA when I do not have info in the cells? I'd like it to populate when it finds data, as of now it shows NA where there is no data.

7. ## Re: Index/Match formula with multiple criteria

Try this:

=IFERROR(INDEX(I9:I12,MATCH(\$B\$2&\$C\$2,G9:G12&H9:H12,0)),"")

8. ## Re: Index/Match formula with multiple criteria

Originally Posted by Whizbang
Try this:

=IFERROR(INDEX(I9:I12,MATCH(\$B\$2&\$C\$2,G9:G12&H9:H12,0)),"")
That was it! Thanks again!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1