VLOOKUP two or more search values
Hey Community!
Can vlookup have more than one search value when referencing another sheet?
Here's an example but returns error.
=VLOOKUP(Participant@row, Milestones@row), ({Milestone Submitted},23,false)
Best Answers
-
Hey @DustyF
VLookUp cannot do what you need but an alternative LookUp Function can. Index/Collect works with multiple criteria.
=INDEX(COLLECT({range that contains data you want returned}, Range1, criteria1, Range2, criteria2, etc),1)
I'm taking a guess at your criteria. Instead of the range being the entire VLookup table in your source sheet, you are only selecting the needed individual columns
=INDEX(Collect({Column23 that you referenced above}, {Participant column in source sheet}, Participant@row, {Milestone column in source sheet}, Milestones@row),1)
If my guess of your criteria is incorrect, a screenshot of your source data would be helpful. Remember since you are dealing with cross sheet references, each cross sheet reference has to be created by you - you cannot simply copy paste my formula into your sheet.
You can find more info about INDEX/Collect here. (You may always want to explore INDEX/MATCH as a direct alternative to VLookUp)
Kelly
-
Thanks Kelly for detailed explanation. That solved it.
Answers
-
Hey @DustyF
VLookUp cannot do what you need but an alternative LookUp Function can. Index/Collect works with multiple criteria.
=INDEX(COLLECT({range that contains data you want returned}, Range1, criteria1, Range2, criteria2, etc),1)
I'm taking a guess at your criteria. Instead of the range being the entire VLookup table in your source sheet, you are only selecting the needed individual columns
=INDEX(Collect({Column23 that you referenced above}, {Participant column in source sheet}, Participant@row, {Milestone column in source sheet}, Milestones@row),1)
If my guess of your criteria is incorrect, a screenshot of your source data would be helpful. Remember since you are dealing with cross sheet references, each cross sheet reference has to be created by you - you cannot simply copy paste my formula into your sheet.
You can find more info about INDEX/Collect here. (You may always want to explore INDEX/MATCH as a direct alternative to VLookUp)
Kelly
-
Thanks Kelly for detailed explanation. That solved it.
-
Glad to help. You may find improved sheet and formula performance if you switch to an Index/Match instead of using the VLookUp. With Index/Match I don't have to worry about moving columns around and possibly disrupting a VLookUp table. Also, performance seems to be increased in cross sheet references since only single columns are involved vs referencing tables of columns. Index/Match is an option to explore - and Index/Collect is by far my most frequently used function combination.
cheers
-
Kelly...it wouldn't appear so but is there a limit to the amount of "COLLECT" a function can have?
When I have two collect critieria, the function works. When I add a third, I get "unparseable".
Here's my two collect - works fine
=INDEX(COLLECT({Milestones Submitted Tracker}, {Milestone Tracker 2}, Participant@row, {SEG Participant Tracker ADMIN Range 3}, Milestones@row), 1)
Here's the same with a third collect - get unparseable
=INDEX(COLLECT({Milestones Submitted Tracker}, {Milestone Tracker 1}, Program Year@row, {Milestone Tracker 2}, Participant@row, {SEG Participant Tracker ADMIN Range 3}, Milestones@row), 1)
-
Hey @DustyF
Is the range of your 3rd criteria in the same source sheet as {Milestones Submitted Tracker}? For the sake of troubleshooting, what happens when you remove criteria 1 and 2 from the formula so you are only left with the one criteria?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!