HLOOKUP searches across the top row of a table and returns a value from a row below it. RANK answers a different question: where one number stands compared with the rest of a list.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when data is arranged horizontally or when numbers need ranking without sorting the sheet.
Look up a value with HLOOKUP
Use HLOOKUP when headers run left to right across the top row.
- Click the result cell.
- Enter =HLOOKUP(lookup_value,table_array,row_index_num,FALSE).
- Use lookup_value for the header you are searching for, such as “March” or a cell reference.
- Use table_array for the full range, including the header row and result rows.
- Use row_index_num for the row inside that range to return.
- Use FALSE for an exact match in most normal workbooks.
For example, =HLOOKUP(“March”,B1:F10,4,FALSE) finds March in the top row and returns the value from the fourth row of that same column.
Rank numbers with RANK
Use RANK or RANK.EQ to show position in a numeric list.
- Click beside the first number to rank.
- Enter =RANK(B2,$B$2:$B$20,0) for highest value as rank 1.
- Use 1 as the final argument when the lowest value should rank first.
- Copy the formula down.
Use absolute references for the ranking range so it does not shift as the formula is copied. Tied values receive the same rank, and the next rank number is skipped.
More control
Fix HLOOKUP errors
#N/A means the lookup value was not found exactly in the top row. Check spelling and hidden spaces. #REF! means the row index is larger than the number of rows in the table array.
Know HLOOKUP limits
HLOOKUP can only return from rows below the matched top row, and row index numbers can break when rows are inserted or deleted.
Use INDEX and MATCH
A horizontal lookup such as =INDEX(B4:F4,MATCH(“March”,B1:F1,0)) is more flexible because it does not rely on a row index number.
Use XLOOKUP when available
XLOOKUP can replace both VLOOKUP and HLOOKUP in newer Excel versions because it searches in more directions and has clearer not-found options.
Sources
- Microsoft Support – HLOOKUP function (2025)
- Microsoft Support – RANK function (2025)
- Microsoft Support – RANK.EQ function (2025)