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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    DustyF ✭✭
    Answer ✓

    Thanks Kelly for detailed explanation. That solved it.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    DustyF ✭✭
    Answer ✓

    Thanks Kelly for detailed explanation. That solved it.

  • Kelly Moore
    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
    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
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!