Lookup using the left portion of target cells

[gmittar]

Hi All,

I'm trying to figure out a formula to lookup using only the left part of the target cell. I could normally easily just add a column to the target, and use LEFT to isolate the characters I need, but in this case, I need to leave the target sheet intact.

Thank you!

EE Number EE Data
1 5 John Jackson
2 7 James Jameson
3 9 Tito McVodka
4 8 Dr. Randy Rose
5 10 John Doe
6 1 Jane Doe
7 2 Jane McDougal
8 4 Finn McFinn
9 3 Kyle McFinn
10 6 Jane Rose

 

[Fluff]

If you want to use index/match
=INDEX(E2:F10,MATCH(B3&"*",E2:E10,0),2)

I din't realise you could use wildcards

 

[gmittar]

I don't think so... In C3 just enter and copy down:

=VLOOKUP("*"&B3&"*",E:F,2,0)

Allow me to clarify. I'll want to look up items both to the left and the right of my anchor column. Vlookup only looks up to the right of the anchor column, so index is the better option for me.

Thanks again.

 

[gmittar]

If you want to use index/match
=INDEX(E2:F10,MATCH(B3&"*",E2:E10,0),2)

I din't realise you could use wildcards

Yep, that works too. Any reason this would be better than the array, or vice versa? I think for continuity sake, not using arrays would be helpful as it's easier for people to understand.

 

[Aladin Akyurek]

Allow me to clarify. I'll want to look up items both to the left and the right of my anchor column. Vlookup only looks up to the right of the anchor column, so index is the better option for me.

Thanks again.

You are not very clear with your specifications...

=LOOKUP(9.99999999999999E+307,SEARCH(B3,$E$2:$E$400),$F$2:$F$400)

 

[Fluff]

Any reason this would be better than the array, or vice versa?

Whilst formulae aren't my strong point, I suspect that on a performance basis the non-array would be better.