Vlookup with multiple column input

Edy Azhar Abdul Rahman

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

Genevieve P.

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

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.

Edy Azhar Abdul Rahman

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

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: Bassam.k@mobilproject.it

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

Genevieve P.

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

Edy Azhar Abdul Rahman

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.

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

Bassam Khalil

@Edy Azhar Abdul Rahman

You are welocome

Celina B

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.

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