about Hlookup, but can not show the first line

welcomemisuki

New Member
Joined
Aug 29, 2020
Messages
12
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
and what is the formula you are using?
 
Upvote 0
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")
 
Upvote 0
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!
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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