Vlookup - when column contains multiple values

[akamie]

Hi all,

Trying to do some scheduling work and struggling...

Basically I have a spreadsheet that in the first column lists a specific number (unique to my industry), and in the 9th column the name of an employee. In a second sheet, I'd like to create a row of names that auto generates by looking up the specific number.

For example:
1st column
18
16
18
20
21
20
7

9th column
Mark
Bob
Jill
Ashley
Sam
David
Peter
Now the second sheet would ideally have a row with the headings "Mark" and "Jill" given a formula that looks up '18' in the 1st column.

Is there a modified version of a lookup formula that can handle duplicate values?

[Haseeb Avarakkan]

Hello akamie, Welcome to the forum!

Assume the numbers in Sheet2 starts on A2, So B2, Confirmed with CONTROL+SHIFT+ENTER, rather than just ENTER, then copy down.

=IFERROR(INDEX(Sheet1!I:I,SMALL(IF(Sheet1!A$1:A$100=$A2,ROW(Sheet1!A$1:A$100)),COUNTIF(A$2:A2,A2))),"")

[akamie]

Thanks! It worked seemlessly.

My only question, is there a way I can modify that to link it to a value, like "18", instead of a cell reference?

The first sheet has multiple values, so when I go to create a 3rd sheet using that formula, but this time for those listed as a "15", it gives me the results for those with "18" instead.

[Haseeb Avarakkan]

If you want to hard code the values enter then in the red highlihted,

=IFERROR(INDEX(Sheet1!I:I,SMALL(IF(Sheet1!A$1:A$100=18,ROW(Sheet1!A$1:A$100)),COUNTIF(A$2:A2,18))), "")