Lookup Brainteaser....

[ExcelQA]

I have a workbook where on TAB 1 there is ONE column with 50 rows, the first 5 characters of each Row I would like to search on another worksheet Tab 2 where I have ONE Row with 50 columns.
There are long names (More than 5 characters) on both rows (Tab2) and columns(Tab1) but I am only interested on the first 5 characters.

How could I perform a Lookup based on Tab 1 Left 5 on tab 2? HOW could i make it dynamic so that the lookup on Tab 2 only considers the first 5 characters?

 

[ExcelQA]

Sample strucrure

 

[dreid1011]

Perhaps this:

Book1
	A	B	C	D	E	F	G	H
2	TAB1
3
4		12345 Candy		10			Lookup the first 5(Left 5) on ROW 15 return row 20
5		23456 Candy		15
6		98345 Candy		20
7		42345 Candy		30
8		54321 Candy		24
9
10
11
12
13	TAB2
14
15		12345 Sidec	98345 Speed	42345 Drive	23456 Kool	54321 Flush
16
17
18
19	Value	10	20	30	15	24
20
21
22
Sheet3

Cell Formulas
Range	Formula
D4:D8	D4	=XLOOKUP(LEFT(B4,5),LEFT($B$15:$F$15,5),$B$19:$F$19,,0,)

 

[ExcelQA]

Perhaps this:

Book1
	A	B	C	D	E	F	G	H
2	TAB1
3
4		12345 Candy		10			Lookup the first 5(Left 5) on ROW 15 return row 20
5		23456 Candy		15
6		98345 Candy		20
7		42345 Candy		30
8		54321 Candy		24
9
10
11
12
13	TAB2
14
15		12345 Sidec	98345 Speed	42345 Drive	23456 Kool	54321 Flush
16
17
18
19	Value	10	20	30	15	24
20
21
22
Sheet3

Cell Formulas
Range	Formula
D4:D8	D4	=XLOOKUP(LEFT(B4,5),LEFT($B$15:$F$15,5),$B$19:$F$19,,0,)

That does it! thank you I don't know why I struggled with it..

 

[dreid1011]

That does it! thank you I don't know why I struggled with it..

You're welcome, it happens to the best of us. Just the other day I struggled to comprehend a minimum rate and I work in accounting. Lots of facepalming ensued.

 

[novit19089]

You're welcome, it happens to the best of us. Just the other day I struggled to comprehend a minimum rate and I work in accounting. Lots of facepalming ensued.

How did you then resolve this situation?

 

[dreid1011]

How did you then resolve this situation?

I asked someone else for help and as I was trying to agree/rebut their answer, it dawned on me that they were right and I was being a huge dummy.