vlookup lookup_value

[ATIHelp]

I've got a very simple Vlookup function that is giving me a problem. I am
trying to find a match to a number, but can only get the match to work if I
enter an apostrophe (') before the number (found in C22) and make it a text.
I've checked that both the source cell and the searched cells are formatted
to identical number format.

=VLOOKUP(C22,Prices,2,FALSE)

"Prices" is a sheet in a second externally linked workbook.

Thanks

 

[Elkar]

I'm guessing that the data in your Prices range is stored as Text. The cells
may be formatted as a Number, but that doesn't necessarily mean that the data
is a Number. Changing a cell format from Text to Number doesn't affect
existing data.

Try this:

In your Prices worksheet, enter a 1 into any blank cell.
Copy that cell
Select your range of data to convert from text to numbers
From the Edit Menu, choose "Paste Special..."
Select the options for 'Values' and 'Multiply'
Click OK
Delete the 1 you entered originally

Now, any text values in that range should be converted to numbers.

HTH,
Elkar

 

[Bob Phillips]

Sounds like Prices key column is text. Fix that or use

=VLOOKUP(TEXT(C22,"@"),Prices,2,FALSE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ATIHelp]

Thanks,

Your answer set off a light bulb on some other things that had been
bothering me. I thought changing cell formats changed the data format. I
made the changes properly and things look good now.

 

[ATIHelp]

Thanks Bob,

Using the "@" in the text function is new to me. Definitely something I can
use in other places.

 

[Harlan Grove]

Sounds like Prices key column is text. Fix that or use

=VLOOKUP(TEXT(C22,"@"),Prices,2,FALSE)

....

Or replace TEXT(C22,"@") with C22&"".