Lookup Formula

[legalhustler]

From row E4:E10 I have the following lookup values:

D3
D4
E22
F13
F2
G2
H15

In Sheet1 from column D4:H4 I have the following headings:

D4: Red
E4: Blue
F4: Green
G4: White
H4 Black

I would like to lookup the first letter from the lookup value for each cell and return the corresponding heading based on the column of the first letter. So the result should for each lookup value would return the following headings:

D3 - Red
D4 - Red
E22- Blue
F13 - Green
F2 - Green
G2- White
H15 - Black

Can someone help? TIA!

 

[Special-K99]

One way using th ASCII value of the first letter in column E

=INDEX(Sheet1!D$4:H$4,1,CODE(LEFT(E4,1))-64-3)

or

=INDEX(Sheet1!D$4:H$4,1,COLUMN(INDIRECT(("Sheet2!"&E4)))-3)
using the column number defined by the first letter in column E

 

[legalhustler]

One way using th ASCII value of the first letter in column E

=INDEX(Sheet1!D$4:H$4,1,CODE(LEFT(E4,1))-64-3)

or

=INDEX(Sheet1!D$4:H$4,1,COLUMN(INDIRECT(("Sheet2!"&E4)))-3)
using the column number defined by the first letter in column E

First method worked pretty well. Second method gave me REF error. INDIRECT is a volatile function and is slower than the first method too. Thanks!

 

[FDibbins]

First method worked pretty well. Second method gave me REF error. INDIRECT is a volatile function and is slower than the first method too. Thanks!

Perhaps you entered the 2nd formula incorrectly, it worked fine for me (both gave the same answers)

INDIRECT is a volatile function and is slower than the first method too

While that may be true, it would depend greatly on the amount of data you are working with, which you did not specify.
On small-ish volumes of data, there would be no discernible difference is speed

 

[legalhustler]

One way using th ASCII value of the first letter in column E

=INDEX(Sheet1!D$4:H$4,1,CODE(LEFT(E4,1))-64-3)

or

=INDEX(Sheet1!D$4:H$4,1,COLUMN(INDIRECT(("Sheet2!"&E4)))-3)
using the column number defined by the first letter in column E

Can we modify the first formula to return values from beyond column Z...such as AC, AD, AE, CX,CY,CZ etc? Thanks