VLookup/Index+Match to get second or third match?
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.
Best Answer
-
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)
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!