about Hlookup, but can not show the first line

welcomemisuki

New Member
Joined
Aug 29, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
I wanna show the level range beside the alphabet, but does not work if I use Hlookup as normal, like the picture.

3.jpg
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
and what is the formula you are using?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
Office Version
  1. 365
Platform
  1. Windows
You could only use HLOOKUP if the Level ranges were at the bottom of the list, although from the picture I think that you will need a completely different method.

With the minimal information provided, best guess as to what you need is

=IFERROR(INDEX($4:$4,AGGREGATE(15,6,COLUMN($C$4:$G$4)/($C$8:$G$16=$B20),1)),"No Match")
 

welcomemisuki

New Member
Joined
Aug 29, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
You could only use HLOOKUP if the Level ranges were at the bottom of the list, although from the picture I think that you will need a completely different method.

With the minimal information provided, best guess as to what you need is

=IFERROR(INDEX($4:$4,AGGREGATE(15,6,COLUMN($C$4:$G$4)/($C$8:$G$16=$B20),1)),"No Match")

Amazing Man, Yes, that is what I need, you so cool, thank you so much!
 

welcomemisuki

New Member
Joined
Aug 29, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Amazing Man, Yes, that is what I need, you so cool, thank you so much!
but can you explain to me how it works? because I want to insert line or add more level to do this table.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
Office Version
  1. 365
Platform
  1. Windows
If you add more to the table then you need to resize the ranges to match.

The bold part should include the Level labels (lookup results), only the columns with the labels. The red part should include the entire row.
The italic part is your lookup value.
The underscored part is the table where the lookup value can be found.

=IFERROR(INDEX($4:$4,AGGREGATE(15,6,COLUMN($C$4:$G$4)/($C$8:$G$16=$B20),1)),"No Match")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,213
Messages
5,594,874
Members
413,945
Latest member
V51773

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
Top