How to replace HLOOKUP with XLOOKUP?

How to replace HLOOKUP with XLOOKUP

Microsoft Excel’s traditional HLOOKUP function was once the go-to tool for performing horizontal lookups searching across a row and returning a value from another row below it. While powerful in its time, HLOOKUP has now been effectively replaced by the newer, more versatile XLOOKUP function.

Since HLOOKUP works horizontally, you simply need to transpose the row into a vertical array, or reference horizontal arrays directly in XLOOKUP.

Syntax XLOOKUP (Horizontal Lookup):

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value: The value you want to search for.

lookup_array: The array or range where to search for the value.

return_array: The array or range from which to return the result.

[if_not_found]: The value to return if no match is found.

Real-Life Example:

Explanation:

=XLOOKUP(B8, B3:L3, B4:L4)

 B8 – The lookup value, which is "Apr" in this case.

B3:L3 – The lookup array, which contains the months from Jan to Dec in the top row.

B4:L4 – The return array, which contains corresponding values for "Sunday" in the row just below the months.

If you enjoyed learning about FILTER FORMULA, there's so much more waiting for you! Dive deeper into the world of Excel with my eBook “10 Ultimate Excel Lookup Formulas” — your complete guide to mastering powerful lookup functions like VLOOKUP, HLOOKUP, INDEX + MATCH, XLOOKUP, LOOKUP, FILTER, CHOOSE and more.

Whether you're a beginner or an advanced user, this book is packed with real-world examples, practical tips, and easy-to-follow explanations to boost your productivity and confidence in Excel.

Start your journey to becoming an Excel pro — just download your copy today !