Lookup against concatenated source value

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

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

=IF(A2="","",CONCATENATE(MID($A2,2,1),MID($A2,3,1)))

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

TIA for any ideas.
Pete
 
Hi Pete

Firstly I'ld change your IF formula to
=IF(A2="","",MID($A2,2,2))
(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
JulieD
 
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.

Pete
 
Back
Top