Any alternative to vlookup function to pull the data

[dinesh.sarsar]

Hi,
I have a file in which sheet 1 has the data of Requirement ID in column A and the Candidate name in column B, the issue that i am facing is, when i use vlookup to pull the data from Sheet 1 column B, into Sheet 2 than as the requirement ID of 198 has got multiple names under it, I get the name of the first person multiple times in sheet 2. So is there any option where i can pull the data as mentioned in the expected result in sheet 2.

[RET]

And why do you have same ID for different data? That is the problem for me. Can you solve that?

[Roger Haney]

Hi, I have a file in which sheet 1 has the data of Requirement ID in column A and the Candidate name in column B, the issue that i am facing is, when i use vlookup to pull the data from Sheet 1 column B, into Sheet 2 than as the requirement ID of 198 has got multiple names under it, I get the name of the first person multiple times in sheet 2. So is there any option where i can pull the data as mentioned in the expected result in sheet 2.

This formula, placed in H1 will correctly identify the names attached to the ID#198 IN Column A:

=IFERROR(INDEX(Sheet1!B$1:B$17,SMALL(IF(Sheet1!A$1:A$17=198,ROW(Sheet1!A$1:A$17),""), ROW() ),1),""). But why, in your "expected results" example, is the last name "Kunal Shah"? Should it not be "Amrutabandhu Chaudhury"? Perhaps I do not fully understand your intent.

[Ed Kelly]

Dinesh believe you want Note 1 (N1). If that is the case consider using PQ instead of VLookup (on small data sets VL is OK however overly taxes excel on larger ones). Bring both datasets into PQ (dropped them into a table first N2/3)). Add a unique index col by concatenating 2 cols N4. N5 remove duplicate items. Then do a left outer join on both tables and expand both columns highlighted N6. Close and load result as a table whilst loading the original tables as connection only (staging tables). Then have a large glass of wine as it is indeed the weekend!

Knock yourself out!

https://www.screencast.com/t/w9qAlVZbAR8G

[pinarello]

If your Excel is new enough, then it is very easy with FILTER and INDEX included in LET.

The fourth expected result in your sheet is wrong.