VLOOKUP two or more search values

DustyF

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)

Kelly Moore

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

DustyF

Thanks Kelly for detailed explanation. That solved it.

Kelly Moore

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

DustyF

Thanks Kelly for detailed explanation. That solved it.

Kelly Moore

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

DustyF

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)

Kelly Moore

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?