VLOOKUP not pulling in correct data when duplicate names

Adriane Price

Hello

I have a line of business with three choices: deploy, consult, and managed services, each associated with all theaters. When someone submits a request through the intake form, they select one of these options, and the form populates the data into the Sub_Org column based on the chosen line of business, and theater. However, if a manager's name appears twice, the formula only picks up the data from the top of the list. I need the formula to search the entire list and populate the correct Sub-Org based on the L5 manager and the associated line of business and theater.

How can I correct my current formula to pull the correct sub-org based on the L5 Manager from the lookup list? I am facing an issue, and I'm unsure how to fix it.

Formula using: =VLOOKUP([Last Name, First Name (VP)]@row, {L5 Manager_Sub-Org Lookup}, 2, false)

Kelly Moore

Hey @Adriane Price

I'm having trouble following what columns are on what sheets in your data collection. If the different columns are all either on the source sheet or the destination sheet, you should be able to pull in the data using a COLLECT without having to concatenate. You cannot use a cross sheet reference range in Concatenate as you're trying to do.

If your Lookup sheet now has the additional columns of theater and Line of business, the COLLECT should work for you. After creating cross sheet references to each of the individual columns in your lookup sheet, you should be able to filter against the appropriate criteria. Your ranges might be named differently but the formula will look something like.

=INDEX(COLLECT({Lookup sheet Sub_Org column}, {Lookup sheet LS Manager}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theater@row, {Lookup sheet Line of Business column}, [Line of Business]@row),1)

What does this get you?

Kelly

Kelly Moore

Hello @Adriane Price

An Index/Collect lookup allows multiple criteria to be used, vs the single criteria that is used in a VLookUp.

=INDEX(COLLECT({LS Manager_Sub_Org sheet Sub_Org column}, {LS Manager_Sub_Org sheet Line of Business column}, [Line of Business]@row, {LS Manager_Sub_Org sheet LS Manager column}, [Last Name, First Name (VP)]@row),1)

I wasn't sure if this filter would be enough, or if you were encountering multiple instances of these same criteria and needed to differentiate amongst them. If this is true, then I use a COUNTIFS instead of the '1' to give me the correct row_index. You can see an example of this method here

Will the formula above work for you?

Kelly

Adriane Price

@Kelly Moore - I did see the other method example today but in my ignorance I am not sure that it will work. If I understand with the other method I would need to add in an "ID" type of column to count which L5 manager would be selected for the Sub-Org column?

In regard to the formula provided above, I received an #INVALID VALUE, which I am not interested in counting but rather pulling in the Sub-Org. Sorry I am not sure I understand.

=INDEX(COLLECT({LS Manager_Sub_Org sheet Sub_Org column}, {LS Manager_Sub_Org sheet Line of Business column}, [Line of Business]@row, {LS Manager_Sub_Org sheet LS Manager column}, [Last Name, First Name (VP)]@row),1)

Lookup sheet has two columns of value that I need L5 Manager and Sub-Org, the Sub-Org column is the one I need to pull in based on the L5 Manager name:

When the submitter uses the intake form they select a "line of business" but this is not in my current formula which I think may be what is causing the mismatch between the "line of business" selection, pulling in the correct Sub-Org that is based on the L5 Manager.

I tried updating to this formula below, but receiving #UNPARESEABLE

Formula in second column (testing which formula will work) =INDEX({L5 Manager_Sub-Org Lookup Org}, MATCH(CONCATENATE(Theatre, "-", [Line of Business]@row, "-", [Last Name, First Name (VP)]@row), CONCATENATE({Theatre column}, "-", {Line of Business column}, "-", {L5 Manager column}), 0))

I need to use two or more criteria when looking up data from the database. There is one person Ramesh MC: who approve for Deploy projects and the other Consult projects. I need to make sure the each Deploy and Consult are unique in the first column of my table. Which I thought I did.... 😔I cannot change the Sub-Org because it is system wide just like each line of business and VP name.

So is where I am having the disconnect between your formula you suggested and the database? I need to create a helper column of sorts?

I attempted to update my lookup sheet as well by adding in a Theater column and Line of Business:

Kelly Moore

Hey @Adriane Price

I'm having trouble following what columns are on what sheets in your data collection. If the different columns are all either on the source sheet or the destination sheet, you should be able to pull in the data using a COLLECT without having to concatenate. You cannot use a cross sheet reference range in Concatenate as you're trying to do.

If your Lookup sheet now has the additional columns of theater and Line of business, the COLLECT should work for you. After creating cross sheet references to each of the individual columns in your lookup sheet, you should be able to filter against the appropriate criteria. Your ranges might be named differently but the formula will look something like.

=INDEX(COLLECT({Lookup sheet Sub_Org column}, {Lookup sheet LS Manager}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theater@row, {Lookup sheet Line of Business column}, [Line of Business]@row),1)

What does this get you?

Kelly

Adriane Price

Hello @Kelly Moore - thank you for reading between the lines and figuring out what obviously I could not describe.

I adapted the formula very slightly to fit all the columns and table I was using. Your formula worked, thank you for the guidance and providing me knowledge to understand.

=INDEX(COLLECT({L5 Manager_Sub-Org Lookup Org}, {L5 Manager_Sub-Org Lookup L5}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theatre@row, {Lookup sheet Line of Business column}, [Line of Business]@row), 1)

Kelly Moore

Excellent! I’m so glad you got it to work. I encourage you to explore the COLLECT function. Although it must be paired with something, it can be used with so many other functions. I think it is one of the most versatile functions in our arsenal.