about Hlookup, but can not show the first line

[welcomemisuki]

I wanna show the level range beside the alphabet, but does not work if I use Hlookup as normal, like the picture.

 

[mole999]

and what is the formula you are using?

 

[jasonb75]

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]

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]

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]

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")

 

[welcomemisuki]

what is the meaning of 15,6 ?

 

[jasonb75]

Options for aggregate, there is a full list here AGGREGATE function