Desperate for help on how to do a VLookup with multiple Lookup Values but only 1 result for each

[Dan Sanchez]

I have a large worksheet ("All Journals Working Copy",) that list all of our items numbers and inventory related stats. Item numbers (Column A) are repeated as we have many warehouses. I have a second worksheet (Item Master Download) on the same spreadsheet (range named "data") that has two pieces of information for each item number that I need to bring over to the first worksheet. Each unique item number although may be repeated but has only one "description" and one "item group" from the "data" range on my second worksheet (Item Master Download). I am not able to do a regular VLookup because many of the item numbers are repeated (due to the same item number being listed separately for multiple warehouses). I am trying to bring the "description" from "Item Master Download" into column B on my "All Journals Working Copy". Every item number listed on "All Journals Working Copy" appear only once on the "Item Master Download".

How can I bring over the corresponding information from the second worksheet "data"? I hope I have provided enough information. Once I am able to do this I will then do a pivot table but I can't run the pivot table until I bring in the values need.

 

[Hui]

Dan

Firstly, Welcome to the Chandoo.org Forums

Can you post a sample workbook for people to review your problem

 

[Dan Sanchez]

Thanks! The worksheet I have showing the two workbooks is 12GB so it won't let me upload it. Any suggestions?

 

[Dan Sanchez]

Attached is a portion of the worksheet.

 

[NARAYANK991]

Hi Dan ,

I am not able to understand what you mean by this :

I am not able to do a regular VLookup because many of the item numbers are repeated (due to the same item number being listed separately for multiple warehouses). I am trying to bring the "description" from "Item Master Download" into column B on my "All Journals Working Copy". Every item number listed on "All Journals Working Copy" appear only once on the "Item Master Download".

VLOOKUP is meant to do a lookup , where the lookup table has unique occurrences of the looked up value.

In your case , how does it matter if the item number occurs repeatedly on the ALL Journals Working Copy tab ? Looking up the repeated item number means the retrieved description will also repeat.

What exactly does the following formula return ?

=VLOOKUP(A2;MyGrid6;2;FALSE)
or
=VLOOKUP(A2,MyGrid6,2,FALSE)

Narayan

 

[Dan Sanchez]

It won't give me a result. VLookup does not like multiple lookup values. Attached is pic.

 

[KenU]

@Dan Sanchez,

Although the sample file given doesn't return a value for every entry, I believe it is because the needed data has been removed to reduce the file size. The formula below should work for the complete data set to return the Description.

=INDEX(MyGrid6[Item name],MATCH('ALL Journals Working Copy'!A2,MyGrid6[Item number],0))

Hope that helps.

Regards,
Ken

 

[Dan Sanchez]

@Dan Sanchez,

Although the sample file given doesn't return a value for every entry, I believe it is because the needed data has been removed to reduce the file size. The formula below should work for the complete data set to return the Description.

=INDEX(MyGrid6[Item name],MATCH('ALL Journals Working Copy'!A2,MyGrid6[Item number],0))

Hope that helps.

Regards,
Ken

I copied the formula exactly like you have it and not value returns. Perhaps I was not supposed to type part of the formula but instead select parts of if?
What is "MyGrid6"?

Attached is a pic.

 

[NARAYANK991]

Hi Dan ,

I do not know what your picture shows ; have you gone into edit mode to show us the formula , or does the cell display the formula at all times ? If so , probably the column has been formatted to TEXT.

See the attached file.

Narayan

 

[Dan Sanchez]

Hi Dan ,

I do not know what your picture shows ; have you gone into edit mode to show us the formula , or does the cell display the formula at all times ? If so , probably the column has been formatted to TEXT.

See the attached file.

Narayan

Narayan,

Where did you get "MyGrid6" as the array? Did you name something "MyGrid6"? That's where I don't follow your formula. Couldn't I just select the Array as 'Item Master Download'!A2:F187506 thus making the formula:

=VLOOKUP(A2,'Item Master Download'!F187506,2)

The above formula is not working as it brings back a "#N/A" result to the first cell "B2.

 

[NARAYANK991]

Hi Dan ,

I think you are not familiar with the syntax for the VLOOKUP function.

Or your posted formula is a typo.

MyGrid6 is the name of the table on the Item Master Download tab , referring to the range :

=$A$1:$E$199

When you select the range A2:E199 on the Item Master Download tab , for the second parameter of the VLOOKUP function , Excel automatically substitutes the named MyGrid6 for the selected range.

The VLOOKUP function will return a #N/A error value if it does not find the looked up value in the lookup range.

Narayan