HLOOKUP on multiple rows

Hi,

sorry, tried to search but I didn't really find a solution.

What I wanna do is enter a value in E1 so it checks both the first rows and the second rows, so that E2 returns 1 for A, B -> 2, C -> 3, D -> 4, E -> 5, F -> 6.
With HLOOKUP it stops checking at C and doesn't continue to check in the row starting with D.

Any ideas? Very much appreciated!

Many thanks!
ManosBildschirmfoto 2021-03-08 um 18.48.51.jpg

Maybe this:

=SUMPRODUCT(--($A$1:$C$3=$E$1),$A$2:$C$4)

This may or may not work... Your profile shows "spreadsheets". That is not an Excel version. Please amend it to show your ACTUAL Excel version.

THIS works for Excel 2010+. Next time... please remember to include represntative data (using numbers as a surrogate for text is not representative!!).

=INDEX($A$1:$C$4,AGGREGATE(14,6,ROW($A$1:$C$4)/(A$1:$C$4=$E$1),1)+1,AGGREGATE(14,6,COLUMN($A$1:$C$4)/(A$1:$C$4=$E$1),1))