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

 

[mole999]

i'm not clear on your desire, =LEFT(cell,4) would return the first four characters and build that into your search ?

 

[Aladin Akyurek]

Cre to elaborate by way of a lookup example?

 

[gmittar]

Thanks for the responses, I've attached a sample file that should illustrate what I'm trying to do. I'm trying to use the numbers in column B (Acc #) to lookup the value in column F(Ranking) by using column E (Account Full).

My normal way to do this would be just to add a column, and use =left(e3,6) and use that as simple vlookup anchor. In this case, I don't want to add a column to the data and i'd like to do it formulaically if possible.

Thank you!

File here
https://sagehospitalityllc.box.com/s/qy2kqp51p51veag42lljfmfg22gztdvr

 

[Fluff]

How about

Excel 2013/2016
	B	C	D	E	F
2	Acc #	Rank		Account Full	Ranking
3	330100	703		330100 SOUTHPARK INCOME	703
4	332000	996		332000 TRUST BANK INTEREST	996
5	332200	21		332200 MASTER LEASE INCOME	21
6	332500	726		332500 CAM INCOME	726
7	332600	419		332600 PARTNER LOAN INTEREST	419
8	332700	67		332700 SOUTHPARK REFI INTEREST	67
9	332900	433		332900 COLEWOOD CENTRE INTEREST	433
10	332200	21		332200 FLORIDA PROCEEDS INTEREST	5
Index

Cell Formulas
Range	Formula
C3	{=INDEX(E$3:F$10,MATCH(B3,LEFT(E$3:E$10,6)*1,0),2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that you have 2 accounts that begin with the same number, so only the first is found

 

[gmittar]

Ah yes, the good old array. Something that I'm still getting used to.

Thanks so much Fluff, this worked! The number duplicate was due to changed number, but I'll keep in mind that it will only find the first.

I appreciate the help!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Aladin Akyurek]

In B2 enter and copy down:

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

 

[gmittar]

In B2 enter and copy down:

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

Thanks for your response. I need to be able to look up both left and right, so an index is going to be the better option for me. I'm using Fluff's suggestion, it's working well.

Cheers.

 

[Aladin Akyurek]

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

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