Lookup against concatenated source value



Am using this formula in B2 to extract the 2nd and 3rd characters in a


It correctly displays a "09"

Would like to do a VLOOKUP against the result displayed as "09" (zero
9) and return another value.

How should the table contain the desired matching value?
If I place a 09 in the first column, it's displayed as a 9 and it will
not be found. Changing to TEXT, and pasting as values doesn't work.
If I place "value" before the formula in B2, it will display as a 9,
and increasing the decimal will display as "9.0".

The text string in A1 looks like 609250412-PL.

So how would I configure the first column in the lookup table to
return a value where the source value is the result of a concatenated

TIA for any ideas.

julied d

Hi Pete

Firstly I'ld change your IF formula to
(can't see why you need to concatenate anything)

Now your question, if I'm understanding it question correctly, you're
having trouble typing 09 in a cell and having it display as 09 rather
than just 9.
Formatting the cells to TEXT and then using Paste Special / Values
(not just paste) should allow you to copy & paste the 09 into the
first column and have it behave.

Hope this helps


Thank you Ron, and JulieD
I took advice from both of your posts, JulieD by removing the
concatenate, and Ron Rosenfeld and JulieD by reconfiguring the lookup
table. It's behaving now. It's nice to have smart people help.

Thank you both.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question