VLOOKUP MATCH Not returning all results

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
Hi Guys.
I wonder if anyone could help to shed some light on something I'm struggling with here.. I'm trying to do a simple VLOOKUP MATCH as follows:
On Sheet 'HomeQuote' I have a DV dropdown in cell D15 that the user selects a model, in this example selecting H1.
('Home Quote'!$D$15)

I have a table called 'QtyTable' on sheet 'Costs'. This has the different models in the top headers along C1:O1 (Headers)
Components are listed in A2:A155, with the quantity of components required for each model listed below the model.

On sheet 'SOL', I am trying to make a component Qty order list for the specific model selected.
Column A has the same components listed as per 'QtyTable' I need the quantities to return for the model in column B

The formula that I have been trying to use in column B on my SOL sheet is:
Code:
=IF(A3="","",VLOOKUP(A3,QtyTable,MATCH('Home Quote'!$D$15,QtyTable[#Headers],0)))
This code is then copied down the rest of the column.

It seems to work for the first 5 rows, where it does return the correct qty for the model selected, but then everything below this is returning zero's, #NA's and random incorrect quantities in cells that shouldn't have a quantity...

I've been going round and round in circles with this, and I'm not getting anywhere...
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,829
Office Version
365
Platform
Windows
From what you describe i cant see any problem that immediately comes to mind. Certainly cant explain randomness as i cant see that thats possible. In the first row that produces an erroneous result check what the MATCH produces and that the lookup value is in the leftmost column of the lookup table.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,894
Office Version
365
Platform
Windows
You haven't specified the final argument in the vlookup formula, so it's doing an approximate match, try
=IF(A3="","",VLOOKUP(A3,QtyTable,MATCH('Home Quote'!$D$15,QtyTable[#Headers],0),0))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,457
Office Version
2019
Platform
Windows
It looks like the problem might be the missing exact match.

=IF(A3="","",VLOOKUP(A3,QtyTable,MATCH('Home Quote'!$D$15,QtyTable[#Headers],0),0))

edit:- must learn to type faster
 
Last edited:

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
You've cracked it! Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,894
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,905
Messages
5,386,694
Members
402,011
Latest member
yousraemara

Some videos you may like

This Week's Hot Topics

Top