VLOOKUP Question/problem

[ANTiSEEN]

I am trying to use VLOOKUP to populate a cell in an invoice.

It looks to a second page with the prices, and they are designated by a #
then a letter, like 1A, 1B, 2A, 2B in column B. Column C is a description,
then the values I want returned are in column D.

My formula is =VLOOKUP(B11,Prices!B482,3) where I enter say 1A into B11
then it should find 1A in the range and return the value from the 3rd column
in the same row as the value 1A.

Problem is, it works on some and brings up the wrong value on others. I am
just trying to get cell B11 to work properly. Why would it bring up the
right value when I enter 30A but return the wrong value when I enter 1A?

Help!

 

[Dave Peterson]

You're looking for an exact match for the value in B11, right?

=VLOOKUP(B11,Prices!B482,3,False)

That 4th parm (false) tells excel that you want that exact match.

 

[Guest]

Hi

Use the last argument for VLOOKUP - which should be FALSE, in your example.
If your list is unsorted it may result in errors otherwise. It will now
return an exact match, or an error.

Hope this helps.
Andy.

 

[ANTiSEEN]

Thanks guys! That was it......

How can I make it show nothing instead of #N/A when there is no value in the
cells in column B?

 

[Guest]

You could wrap it in an IF formula:
=IF(ISERROR(VLOOKUP(B11,Prices!B482,3,false)),"",VLOOKUP(B11,Prices!B482,3,false))

Andy.

 

[smonczka]

Yes, I ran into the same problem myself, look up the formula for ISNA,
such as...

=IF(ISNA(VLOOKUP(A1,product,3,FALSE)),0,(VLOOKUP(A1,product,3,FALSE)))

in English...

If the VLookup returns N/A then return 0 otherwize return what ever was
looked up.

Hope that helps.

Steve

 

[Tim]

Thanks again, I am sooooo rusty at this..........

One last question for this sheet. When I try to drag the formula down all
the cells increment up. What can I do to freeze some (the range where the
data is coming from) and let others climb (column B where I enter the
qualifier)?

 

[Guest]

You need to use absolute references:
=IF(ISERROR(VLOOKUP(B11,Prices!$B$4:$D$82,3,false)),"",VLOOKUP(B11,Prices!$B$4:$D$82,3,false))

Andy.

 

[Tim]

OK one more 'last' question...

In column K I total the result of the VLOOKUP * the quantity I enter into
column J, but if there's nothing in J it shows, #VALUE! It's just =I11*J11

How do I wrap that in an IF formula?

 

[Doug]

Thanks again, I am sooooo rusty at this..........

One last question for this sheet. When I try to drag the formula down all
the cells increment up. What can I do to freeze some (the range where the
data is coming from) and let others climb (column B where I enter the
qualifier)?

Put $ signs in front of any numbers you want to fix.

 

[Tim]

I thought it was $ but when I tried it I did it like $B11 instead of $BE$11

THANKS!!!!!!

 

[Guest]

Just check if J11 has anything in it:
=IF(J11<>"",I11*J11,"")

Andy.