Lookup Formula

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
Platform
  1. Windows
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top