Curious VLOOKUP problem

[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

 

[Gav Dent]

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.

 

[Gav Dent]

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

 

[Bill D]

Thanks Gav - worked a treat!!

Bill D

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

 

[Ron Rosenfeld]

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

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

 

[Bill D]

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

 

[Frank Kabel]

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

HTH
Frank