Vlookup with multiple column input

Hi,

I have a database that is updated everyday and i am looking for an entry(employee name) with multiple input criteria. i have tried this formula in excel but not working in smart sheet. =VLOOKUP(([PII ID]@row)&([Run]@row)&([Tech]@row), {BKR 28 feb Range 4}, 14, false).

Can anyone help me to solve this problem.

Thank you

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Edy Azhar Abdul Rahman

    Instead of a VLOOKUP which looks across one big range, you'll want to use an INDEX(COLLECT.

    The way it works is that the COLLECT function first lists the column you want to bring back information from, then you subsequently identify each column & specific value to match afterwards.

    Try something like this:

    =INDEX(COLLECT({Team Leader Name}, {Pii No Column}, [PII ID]@row, {Run Column}, Run@row, {Tech Column}, Tech@row), 1)


    Note that each of {these ranges} are one specific column, versus a range like in a VLOOKUP. It will also look just for the first match. If you have multiple potential matches, then you'll want to do a JOIN(COLLECT as Bassam mentioned, since this will Join together all your matches into one cell.

    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Edy Azhar Abdul Rahman 

    Hope you are fine, you can use Join With Collect function to do that in smartsheet.

    Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi Bassam,

    I would like to get the "Team Leader" information into [2021 data tracking] sheet from [BKR 28] sheet

    From another worksheet [BKR 28]

    Sometimes we have the same [PII ID] (AA) but different [Run] (z) or Different [Service code] (Y). For Different runs and different service code we have different [Team Leader](XXX). How do i want to match the correct Team Leader to the correct Pii ID, Run and Service code?

    What Vlookup is giving me the top/first entry of the PII ID.

    I hope you can help me, without creating more colums

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/19/21

    Hi @Edy Azhar Abdul Rahman

    Could you please share me as an admin on a copy of those sheets (after removing or replacing any sensitive information). and i will create the exact formulas for you.

    My Email: [email protected]

    in your case, you need to use Join With Collect Function.Vlookup will not work for multi criteria.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Edy Azhar Abdul Rahman

    Instead of a VLOOKUP which looks across one big range, you'll want to use an INDEX(COLLECT.

    The way it works is that the COLLECT function first lists the column you want to bring back information from, then you subsequently identify each column & specific value to match afterwards.

    Try something like this:

    =INDEX(COLLECT({Team Leader Name}, {Pii No Column}, [PII ID]@row, {Run Column}, Run@row, {Tech Column}, Tech@row), 1)


    Note that each of {these ranges} are one specific column, versus a range like in a VLOOKUP. It will also look just for the first match. If you have multiple potential matches, then you'll want to do a JOIN(COLLECT as Bassam mentioned, since this will Join together all your matches into one cell.

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Hi @Genevieve P ,

    It works perfectly. Thank you so much.


    Hi @Bassam Khalil,

    Thank you for your help.


    Really appreciate both you help.

    Thank you and enjoy your and stay safe. 😊

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem at all, I'm glad this worked for you! 🙂

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Edy Azhar Abdul Rahman

    You are welocome

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi @Genevieve P.,

    I'm new to Smartsheet so I'm struggling a bit with how to get this formula to work properly in my case below. The sheet below is updated each week using Data Shuttle. I have a separate sheet where I need to pull the counts.

    I would like to use your formula but I don't know how to "identify each column & specific value" correctly. I'm trying to get the [Count] from this sheet for example when the Org is "MSCL", Type is "M", and Metric Category is "01 - Active". I was thinking I could use your formula but I'm not sure exactly how to get the specific values. Any help is greatly appreciated.


    Thanks so much!!

    Celina


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Celina B

    When you say you want to "get the Count", are you looking to SUM the values in the "Count" column together based on your criteria? Or do you want to collect all the matching values as separate numbers in one cell?

    To Sum them, you would want to use a SUMIFS function:

    =SUMIFS({Column to Sum}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2")

    So in your case:

    =SUMIFS({Count Column}, {Org Column}, "MSCL", {Type Column}, "M", {Metric Category Column}, "01 - Active")


    Or if you want to see the individual values, try a JOIN(COLLECT:

    =JOIN(COLLECT({Column with values to return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

    So in your case:

    =JOIN(COLLECT({Count Column}, {Org Column}, "MSCL", {Type Column}, "M", {Metric Category Column}, "01 - Active"), ", ")


    Let me know if that helped!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!