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
Check out the Formula Handbook template!