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!B4:D82,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!B4:D82,3,False)

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

ANTiSEEN wrote:
>
> 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!B4:D82,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

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" <[email protected]> wrote in message
news:[email protected]...
>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!B4:D82,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!
>

[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?


<Andy> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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!B4:D82,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!
>>
>
>

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

Andy.

"ANTiSEEN" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
> <Andy> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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!B4:D82,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!
>>>
>>
>>
>
>

[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)?


<Andy> wrote in message news:[email protected]...
> You could wrap it in an IF formula:
> =IF(ISERROR(VLOOKUP(B11,Prices!B4:D82,3,false)),"",VLOOKUP(B11,Prices!B4:D82,3,false))
>
> Andy.
>
> "ANTiSEEN" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>> <Andy> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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!B4:D82,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!
>>>>
>>>
>>>
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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)?
>
>
> <Andy> wrote in message news:[email protected]...
>> You could wrap it in an IF formula:
>> =IF(ISERROR(VLOOKUP(B11,Prices!B4:D82,3,false)),"",VLOOKUP(B11,Prices!B4:D82,3,false))
>>
>> Andy.
>>
>> "ANTiSEEN" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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?
>>>
>>>
>>> <Andy> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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!B4:D82,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!
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[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?


<Andy> wrote in message news:[email protected]...
> You could wrap it in an IF formula:
> =IF(ISERROR(VLOOKUP(B11,Prices!B4:D82,3,false)),"",VLOOKUP(B11,Prices!B4:D82,3,false))
>
> Andy.
>
> "ANTiSEEN" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>> <Andy> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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!B4:D82,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!
>>>>
>>>
>>>
>>
>>
>
>

[Doug]

"Tim" <[email protected]> wrote in message
news:[email protected]...
> 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!!!!!!

<Andy> wrote in message news:e5eh4%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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)?
>>
>>
>> <Andy> wrote in message news:[email protected]...
>>> You could wrap it in an IF formula:
>>> =IF(ISERROR(VLOOKUP(B11,Prices!B4:D82,3,false)),"",VLOOKUP(B11,Prices!B4:D82,3,false))
>>>
>>> Andy.
>>>
>>> "ANTiSEEN" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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?
>>>>
>>>>
>>>> <Andy> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>>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!B4:D82,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!
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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

Andy.

"Tim" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
> <Andy> wrote in message news:[email protected]...
>> You could wrap it in an IF formula:
>> =IF(ISERROR(VLOOKUP(B11,Prices!B4:D82,3,false)),"",VLOOKUP(B11,Prices!B4:D82,3,false))
>>
>> Andy.
>>
>> "ANTiSEEN" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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?
>>>
>>>
>>> <Andy> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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!B4:D82,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!
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>