VLOOKUP for same value with multiple results

Hi All,

I recently found an error in my formula below that skipped over the same name though it had a different correlated value. It should have brought back N/A instead of the same result as the one above it. I have attached the exampel with fake names for use. Thank you for your ideas! I am sure its a simple fix, but appreciate the help.

=VLOOKUP(A2&"*",'2'!A:B,2,FALSE)

Please see attached!

I think you forgot the attachment. The yellow banner at the top of the screen gives details of how to do this.

Pete

EDIT - Ah, I see you have done this now.

Pete

Please try,

Formula:

Please Login or Register  to view this content.

A different approach which may be more recalculation efficient.

Your list of names in '1'!A2:A... could have multiple instances of the same name. If you want only the 1st/topmost to return a VLOOKUP value, try

C2:

Formula:

Please Login or Register  to view this content.

Fill down as far as needed.

This one worked, but only for two rows. How can we edit to make it for as far as we need to drag it down?

=IFERROR(INDEX('2'!$B$2:$B$3,AGGREGATE(15,6,(ROW('2'!$A$2:$A$3)-ROW('2'!$A$2)+1)/('2'!$A$2:$A$3='1'!A2),COUNTIF($A$2:A2,A2))),"N/A")

The same occured with the first reply. I will need one of these to apply to as many applicable rows as possible. I apologize if the example was misleading.

=IF(COUNTIF(A$2:A2,A2&"*")=1,VLOOKUP(A2&"*",'2'!A:B,2,FALSE),NA()).

You can extend the formula to the last row or some row much further as below.

=IFERROR(INDEX('2'!$B$2:$B$10000,AGGREGATE(15,6,(ROW('2'!$A$2:$A$10000)-ROW('2'!$A$2)+1)/('2'!$A$2:$A$10000='1'!A2),COUNTIF($A$2:A2,A2))),"N/A")

The issue witht he above occurs when I have three names in a row with different items. With the formula above, sheet 1 skipped over an item that was supposed to be "N/A", replaced it with a match from Line 2, and line 3 was N/A instead. I have attached an example.

Originally Posted by phatdear

You can extend the formula to the last row or some row much further as below.

=IFERROR(INDEX('2'!$B$2:$B$10000,AGGREGATE(15,6,(ROW('2'!$A$2:$A$10000)-ROW('2'!$A$2)+1)/('2'!$A$2:$A$10000='1'!A2),COUNTIF($A$2:A2,A2))),"N/A")

The issue witht he above occurs when I have three names in a row with different items. With the formula above, sheet 1 skipped over an item that was supposed to be "N/A", replaced it with a match from Line 2, and line 3 was N/A instead. I have attached an example.

Not really sure if this is what you want. Try this,

=IFERROR(INDEX('2'!$B$2:$B$10000,AGGREGATE(15,6,(ROW('2'!$A$2:$A$10000)-ROW('2'!$A$2)+1)/('2'!$A$2:$A$10000='1'!A2)/('2'!$B$2:$B$10000='1'!B2),COUNTIFS($A$2:A2,A2,$B$2:B2,B2))),"N/A")

or if the data are unique, then maybe

=IFERROR(LOOKUP(9^9,SEARCH('1'!A2,'2'!$A$2:$A$10000)+SEARCH('1'!B2,'2'!$B$2:$B$10000),'2'!$B$2:$B$10000),"N/A")

If these are not what you are looking for. Please attach a file with more sample data and expected results.

That seemed to fix it (the second formula). I do have unique items and it lined up the way I preferred for our report. Thank you again for all the help!