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.
Last edited by dta1984; 11-21-2011 at 03:53 PM.
This link will give you the formula you need.
http://support.microsoft.com/kb/214142
Read these articles for more information on INDEX.
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
Last edited by Whizbang; 11-18-2011 at 12:49 PM.
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.
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!Originally Posted by Whizbang
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.
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks