VLookup not working

[Scott]

Hello,

I'm having a problem with my vlookup formula so I could use some help. I
have already confirmed that the formatting is the same in the two lists
(Format is Number-> General). I have also confirmed that there are no leading
or trailing characters using the LEN() formula. When I pastespecial-> formats
between the referenced cells, the lookup does not work but, when I paste the
referenced cells, then the lookup works. The problem is that I don't want to
paste 1,000 separate times.

Any other ideas? For example, when I do a lookup on sku 123, Excel returns
nothing (no #ERROR and no #NA).

SKU QTY
123 200
087 10

Thanks for your help.
Scott

 

[Niek Otten]

Hi Scott,

Either your search argument or your table is text. Formatting doesn't cure that.

First find out which one is text, with the ISTEXT() function.

Format an empty cell as number and enter the number 1. Edit>Copy.
Select your text-numbers, Edit>Paste Special, check Multiply.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I'm having a problem with my vlookup formula so I could use some help. I
| have already confirmed that the formatting is the same in the two lists
| (Format is Number-> General). I have also confirmed that there are no leading
| or trailing characters using the LEN() formula. When I pastespecial-> formats
| between the referenced cells, the lookup does not work but, when I paste the
| referenced cells, then the lookup works. The problem is that I don't want to
| paste 1,000 separate times.
|
| Any other ideas? For example, when I do a lookup on sku 123, Excel returns
| nothing (no #ERROR and no #NA).
|
| SKU QTY
| 123 200
| 087 10
|
| Thanks for your help.
| Scott
|

 

[Matt Richardson]

Hello,

I'm having a problem with my vlookup formula so I could use some help. I
have already confirmed that the formatting is the same in the two lists
(Format is Number-> General). I have also confirmed that there are no leading
or trailing characters using the LEN() formula. When I pastespecial-> formats
between the referenced cells, the lookup does not work but, when I paste the
referenced cells, then the lookup works. The problem is that I don't want to
paste 1,000 separate times.

Any other ideas? For example, when I do a lookup on sku 123, Excel returns
nothing (no #ERROR and no #NA).

SKU QTY
123 200
087 10

Thanks for your help.
Scott

If you are pasting special and then formats, you are pasting nothing
more than the cell format - you aren't actually pasting the function
or cell contents. Just a straightforward copy and paste should do the
trick. You can deal with your number formatting later if that's part
of the problem.

Hope I'm on the right track

Regards,
Matt Richardson
http://teachr.blogspot.com