Lookup against concatenated source value

[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

 

[julied d]

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

 

[Ron Rosenfeld]

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

If the first column in the lookup table, and the 2nd and 3rd characters in A2 will always be numbers, then you have several options.

You can enter the values in column 1 of your lookup table as numbers, and convert the formula to a number:
e.g:

=VLOOKUP(IF(A2="","",--MID(A2,2,2)),lookup_table,column_number,FALSE)

If either of those values might be text, then the values in column 1 of the lookup table must also be text. Numbers can be entered as text either by formatting the cell as text BEFORE entering the 09, or by preceding the 09 with a single quote (which wil not show in the cell: '09 )

 

[Pete]

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

 

[Ron Rosenfeld]

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.

Glad to help. Thanks for the feedback