Lookup - Formatting of numbers

[GRS]

I currently have a lookup field in my spreadsheet that looks up the
price of a product by product number.

Although the lookup usually works there are occasions when it returnes
the N/A even tho the product number is in the lookup table.

If I copy the product reference from the lookup table and paste it into
the first spreadsheet, the lookup then gives the correct price.

I've tried formatting the product reference field in the spreadsheet
and in the lookup table to try to get them to match, but i still get
these errors.

Can anyone shed any light?

thanks

Gill

 

[BenjieLop]

Most of the time, adding the word "False" at the end of the vlooku
formula helps me keep my sanity.

For example, the formula will look like this:

=vlookup(B1,$A$1:$A$100,2,false)

... hope this will help you.

 

[JE McGimpsey]

Some of your product numbers are probably entered as text (this can
happen when the cell is preformatted as text, or when data is pasted
from a web page). To coerce them back to numbers, Copy a blank cell,
select your product numbers, choose Edit/Paste Special, and select the
Add and Values radio buttons. Click OK.

 

[GRS]

thanks for the reply. My lookup is like this...

=INDEX(Prices!$B$1:$D$735, MATCH(D468,Prices!$B$1:$B$735,), MATCH("Cos
Price",Prices!$B$1:$D$1,))


humm...maybe it isn't a lookup then????

Where would I put the 'false' and regain MY sanity!

thanks

Gil

 

[BenjieLop]

Based on your notations, the following are what I can conclude:

1. Your table is in another Worksheet named "Prices"
2. Your table range is B1735
3. The product number to be looked up is in cell D468

Furthermore, I will assume that the price you want your formula to giv
you is in (Prices!) column D.

Based on the above criteria, see if the following formula will help yo
in any way:

=vlookup(D468,Prices!$B$1:$D$735,3,false)

The "false" is always the last argument in the vlookup formula.

 

[Myrna Larson]

MATCH is similar in principle to VLOOKUP. You would use FALSE or 0 as the 3rd
argument in the MATCH formulas. But your problem is most likely the table
entries or D468 contains text rather than a number.

 

[Dave Peterson]

And you actually have 0's (or falses) in your formulas:
MATCH(D468,Prices!$B$1:$B$735,)

That last comma doesn't have anything after it.

You get the same effect with:

=IF(A1="a","hi",)

So, I, too would look for differences in either spelling (or text v. numbers).