VLookup/Index+Match to get second or third match?

Kohl Methven

Hello,

I apologize if this has been asked before but I'm struggling to find a solution that works. I have two separate sheets, one that catalogs all computers in our organization and the other is a "master inventory" sheet. I can use VLookup and/or Index+Match to retrieve the first value in the computer inventory sheet that matches an employee's name but I'm struggling to figure out how to get the second or third value that matches their name. The following formula will find the first one but what can I add or how can I adapt it to find the second or third match.

=INDEX({ActiveComputerData Range 4}, MATCH(EmployeeName@row, {ActiveComputerData Range 1}), 0)

I've tried to get a helper column setup but I can't figure out how to make them unique (i.e. EmployeeName + number of times the exact employee name has appeared above in the list.) and then later call upon the helper column in the master inventory sheet. Any help would be appreciated.

Leibel S

@Kohl Methven

Use an Index Collect combination. See below to get the second value. Change the bolded 2 to a different number to get another result.

=INDEX(COLLECT({ActiveComputerData Range 4},{ActiveComputerData Range 1},EmployeeName@row),2)

Leibel S

@Kohl Methven

Use an Index Collect combination. See below to get the second value. Change the bolded 2 to a different number to get another result.

=INDEX(COLLECT({ActiveComputerData Range 4},{ActiveComputerData Range 1},EmployeeName@row),2)