Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Missing data with Vlookup

0

My formula with the vlookup is not displaying all the data in my list. 

Sometimes I have three warehouses with a count for the same item, but not all warehouses are showing the counts. 

My formula is not giving 100% accuracy. Attached is the spreadsheet. 

Answer
Discuss

Discussion

Hi Lorie.

Please see my Answer below.

Next time, please remember to include your full question above- contributors and other users should not need to open your file to see the real question.
John_Ru (rep: 6102) Oct 3, '22 at 6:23 pm
Okay, I understand. Thank you very much. It works perfectly!
lorie excels (rep: 2) Oct 10, '22 at 5:51 pm
Great! 
John_Ru (rep: 6102) Oct 10, '22 at 6:07 pm
Add to Discussion

Answers

0
Selected Answer

Hi Lorie and welcome to the Forum

The bold part below in your formula (e.g. in cell D3):

=IFERROR(VLOOKUP(@$B:$B,$N:$AA,6,0),"")

attempts to look up B3 in coulmn N of the Warehouse table and return column 6 (=S).

The problem is that the value of B3 (M000001) is not unique- it appears in column N rows 3. 4, 5 and 6. Excel this returns only the first match and you have "missing figures".

In the attached file, I've fixed that by making D3 look up the unique value in your column Q (Join) by concatenating the column heading (e.g. D2) to the value of B3 and looking in a redefined range. The formula in D3 is:

=IFERROR(VLOOKUP($B3 & " - " & D$2, $Q:$AA,3,0),"")

The column index is increased per column up to L then the formulae copied down.

REVISION:

Given you later stated that the VLOOKUP should be done on the hidden sheet "Bulk", the above formula needs to be changed to refer there. Range references to $Q:$AA become Bulk!$D:$N so the formula in cell D3 iof your List sheet becomes

=IFERROR(VLOOKUP($B3 & " - " & D$2,Bulk!$D:$N,3,0),"")

This is implemented in the second file below, "work with VLOOKUP on hidden sheet v0_b.xlsx".

Hope this works for you.

Discuss

Discussion

Thank you so much! It works perfect, but I forgot to mention there is a hidden sheet called "Bulk". That's where I really need to have the lookup from. I put them side by side so I can see what I was doing. I'm not sure what I'm doing wrong, and I cannot figure out why it's still not populating with the BULK sheet.
lorie excels (rep: 2) Oct 3, '22 at 10:24 pm
Thanks for selecting my Answer, Lorie. Please see my revised Answer and second file.

Next time, please be sure to put a full detail in the question (and not hide essential description in the file)
John_Ru (rep: 6102) Oct 4, '22 at 4:13 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login