Curious VLOOKUP problem

  • Thread starter Thread starter Bill D
  • Start date Start date
B

Bill D

Hi,

This would be almost impossible to descibe without seeing the worksheet so
if anyone would be good enough to take a look here -
http://www.alanancy.com/QUOTES.xls

The Excel worksheets (just experimenting at this stage so not a finished
item) are to be used as a temporary measure to illustrate information as
part of a simple quotation tool by using a VLookup table.

Worksheet 'INPUT' is where information on the product is entered with the
Model column acting as the variable for the VLookup data.

The 'QUOTE' sheet pulls in all of the information, according to the model
entered, from the 'DATA' sheet.

It's quite simplistic really although the problem is that for the
'Commercial' products in the data sheet, they are not recognised even though
they are entered on the input sheet identically to how they are saved on the
lookup table.

If anyone can understand this I'd be grateful for a pointer in the right
direction as to why this doesn't work??

Thanks

Bill D
 
I think you need the column you are doing the Vlookup on to be the first
column on the table.

Try that and see how you get on.
 
Sorry, ignore that! Was looking at wrong sheet in your workbook!

What you need to do is replace the bit in the formula which is
DATA!$B$6:$I$19 with the word table which is your range name.

Cheers
 
Thanks Gav - worked a treat!!

Bill D


Gav Dent said:
Sorry, ignore that! Was looking at wrong sheet in your workbook!

What you need to do is replace the bit in the formula which is
DATA!$B$6:$I$19 with the word table which is your range name.

Cheers
Click to expand...
 
Hi,

This would be almost impossible to descibe without seeing the worksheet so
if anyone would be good enough to take a look here -
http://www.alanancy.com/QUOTES.xls

The Excel worksheets (just experimenting at this stage so not a finished
item) are to be used as a temporary measure to illustrate information as
part of a simple quotation tool by using a VLookup table.

Worksheet 'INPUT' is where information on the product is entered with the
Model column acting as the variable for the VLookup data.

The 'QUOTE' sheet pulls in all of the information, according to the model
entered, from the 'DATA' sheet.

It's quite simplistic really although the problem is that for the
'Commercial' products in the data sheet, they are not recognised even though
they are entered on the input sheet identically to how they are saved on the
lookup table.

If anyone can understand this I'd be grateful for a pointer in the right
direction as to why this doesn't work??

Thanks
Click to expand...

Your formula is looking in the table $B$6:$I$19, but your commercial products
are in rows 20-27. Change the VLOOKUP formulas to reflect that. For example:

C9: =VLOOKUP(INPUT!F7,DATA!$B$6:$I$27,2,FALSE)
--ron
 
That problem is now solved thanks but here's a good one.....

In the new example (http://www.alanancy.com/QUOTES.xls) is there any way I
can stop Excel inserting the zeros and #N/A text in the now empty cells
where there is no product (i.e the quote, as in this new example, is only
for pumps P1-P6)

Thanks in advance

Bill D
 
Hi bill
use the following formula type:
=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(...))

HTH
Frank
 
Back
Top