IF Lefts - oh my

[vullkunnraven]

I have criteria in column a:

a2:111
a3:113
a4:111
a5:112

columns b,c,d are:

b2: 111_123_xyz | c2: 113_123_xyz | d2: 112_123_xyz
b3: 111_123_xyz | c2: 113_123_xyz | d2: 112_123_xyz
b4: 111_123_xyz | c2: 113_123_xyz | d2: 112_123_xyz
b5: 111_123_xyz | c2: 113_123_xyz | d2: 112_123_xyz

Now - I would like to return in column e the result based on column a.

For example, e3 would equal 113_123_xyz.

Any idea how to do this? I tried using left to extract the first three characters and doing an if formula, but I can't figure out how to have excel look over 3 columns.

Thanks

 

[xld]

Try this array formula

=INDEX(B2:D2,MATCH(A2,--(LEFT(B2:D2,LEN(A2))),0))

 

[vullkunnraven]

thanks XLD, but I just got value# error.

I tried doing ctrl shift enter too - no go

 

[vullkunnraven]

OH WAIT!

I took out len and it worked!



Dang - your good XLD!

You rock!

 

[meldoc]

Hello

The formula works.

Paste it into E3 then tap the F2 key, so that you can see the formula in the cell, then hold down Ctrl+Shift then tap Enter.