Learn HLOOKUP in Excel with Free Lessons & Tips

To use `HLOOKUP` in Excel, you basically tell Excel to find a value at the top of a list and then grab related information from a specific line below it. So, if you're looking for sales in March from a table, you'd pick "March" as your search word, tell Excel where your table is, say which line of information you want (like sales numbers), and then tell it to find an exact match. For instance, by writing `HLOOKUP("March", A1:G4, 2, FALSE)`, you're asking Excel to find "March" at the top of your table in A1:G4 and give you the sales numbers from the line right below where it found "March". Just pop that formula into a cell, hit Enter, and Excel does the rest. read less

To use `VLOOKUP` and `HLOOKUP` together with an `IF` in Excel, you make a formula that chooses between looking up data vertically or horizontally based on a condition. For instance, if a cell says "Vertical," it does a `VLOOKUP` to find and return data from a column. If it says "Horizontal," it does an `HLOOKUP` to find and return data from a row. This lets you switch between search directions dynamically, based on what you specify, making your data lookup adaptable. read less

To auto-link values with `HLOOKUP` in Excel, use a formula that finds a value at the top of your data and returns a related value from another row. For example, `HLOOKUP(A1, A3:E8, 3, FALSE)` searches for what's in A1 across the top of A3:E8 and gives back the value from the 3rd row in that column. The `FALSE` means it looks for an exact match. If you change the value in A1, the result from `HLOOKUP` automatically updates, linking your data dynamically. read less

`INDEX-MATCH` is better than `VLOOKUP` or `HLOOKUP` because it's more flexible and faster, especially with big tables. It can find data in any direction and won't break if you add or remove columns, unlike `VLOOKUP` which can only look right and needs manual updating. `INDEX-MATCH` also works better for tricky searches and can grab data from anywhere, not just to the right of your search value, making it a more versatile choice for finding and organizing your data in Excel.

read less

3 things you should know about Excel HLOOKUP function
Whenever you do a horizontal lookup in Excel, please remember the following facts:

The HLOOKUP function can only search in the top-most row of table_array. If you need to look up somewhere else, consider using an Index / Match formula.
HLOOKUP in Excel is case-insensitive, it does not distinguish uppercase and lowercase.
If range_lookup is set to TRUE or omitted (approximate match), the values in the first row of table_array must be sorted in ascending order (A-Z) left to right.

read less

The easiest why to do that is to have a column with the return position you want. So:

Hlookup(A1,B1:D20,zz1,False)

cell zz1 has: 2
cell zz2 has: 3

etc...

If you don't like that you can add 2 plus row() and maybe subtract to get where you want when you copy.

read less