# Lookup Formula

#### legalhustler

##### Well-known Member
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!

Last edited:

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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

Last edited:
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!

Last edited:
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

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

Last edited:

Replies
1
Views
425
Replies
0
Views
503
Replies
5
Views
298
Replies
1
Views
802
Replies
9
Views
1K

1,196,318
Messages
6,014,614
Members
441,832
Latest member
tony tessman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back