Excel Hlookup, it’s Vlookup’s forgotten brother or sister (depends who is asking). It’s not as popular as Vlookup but it’s important that you are aware of it’s abilities. Vlookup finds a match on the 1st column and pulls the data based on the #of columns you define. Hlookup does the same only for rows.
Excel Hlookup example
For this example let’s look at table #2 :
Table #2 is sorted descending based on the population column. Here we can use Hlookup to return the city by rank of population. For example If I want to return the x biggest city in that table I can use Hlookup for that. See below:
In table 1 I am pulling the data from table 2 using Vlookup:
Here you see that City is the key connecting between the two tables. Based on the #of rows I want to go from top to down I get the value.
Excel Hlookup code breakdown for easy copying
Column H = HLOOKUP(H$16,$B$16:$E$23,G20,0)
Detailed explanation of the function Hlookup
1st argument – The value you are looking for a match
2nd argument – The range where the you are looking for a match and return value. Be aware that the search will happen on the first row of the range – in this case 16. Make sure the range covers any row you want to pull data from.
3rd argument – #of rows top to bottom that you want to pull the data from. 1 is the minimum value and it will return the same value you are searching for.
4th argument – define if the match should be exact or closest sorted in ascending order.
Common errors while using Hlookup
If you get the result #Ref! , that means that argument #3 (# of rows top to bottom) is greater than the #of rows in argument #2 (the range you inputted).
If you get the result #N/A , that means that the value you are searching for (argument #1) does not exist.
Using Hlookup in Excel is less familier but it does have it’s advantages. Espicieally if the layout of the data requires a horizontal search, but there are better functions to use. I highly recommend you try Index + Match. They are covered in the link below.