Explanation of the HLOOKUP function
The image below shows the 4 arguments that the HLOOKUP function is asking for, each color coded to help you follow along:
- lookup_value (blue)
- table_array (red)
- row_index_num (green)
- range_lookup (yellow)
Our data is some made up condo prices in Toronto, Calgary and Vancouver between 2015 to 2017.
Video – How to use a HLOOKUP in Microsoft Excel
HLOOKUP explanation continued
1. lookup_value (boxed in blue)
In our example the HLOOKUP function is referencing the lookup_value in cell G7 (boxed in blue). The value in cell G7 is “Calgary” and this is the value that our HLOOKUP function will be searching for in our table_array.
As an alternative you could write “Calgary” (has to be in quotations because it is text) directly into the HLOOKUP function and it would work the exact same. To do this you would replace G7 with “Calgary”.
Note: It is easier to make changes to your lookup_value when your HLOOKUP function references the value in a cell because you can easily change your lookup_value and Microsoft Excel will automatically calculate the new result.
2. table_array (boxed in red)
In our example the table_array is B6:E9 (boxed in red) because this is our data range we want to search in. When using a HLOOKUP function you can either manually type your data range or select your data range using your mouse.
IMPORTANT: your lookup_value must exist in the top row (the 1st row) in your data range. This means that your table_array always has to start with the row that includes the data for the lookup_value you are searching for.
3. row_index_num (numbered in Green 1 through 4)
This argument tells the HLOOKUP function which row to return the value from. In our example it’s 3 meaning the result will come from the 3rd row in our table_array which is the “2016” row. You always start counting from top to bottom with the top row in your table_array as row number 1. If we wanted to bring back the condo price in “2017” instead, our row_index_num would need to be 4.
4. range_lookup (underlined in yellow)
You have 2 choices depending on how you plan to use a HLOOKUP function:
- TRUE (or you can type 1) – will search for the closest match to your lookup_value
- FALSE (or you can type 0) – will search for an exact match to your lookup_value
Those 2 choices are manually typed by you into your HLOOKUP function.
Note: Always use the FALSE argument if you want to find an exact match against your lookup_value.
So what exactly is the HLOOKUP function doing?
- Step 1 – the HLOOKUP function searches for your lookup_value in the top row of your table_array (and only within the top row) starting with the 1st column and working its way across each column (moving to the right) in the top row until it finds either:
- an exact match if you used FALSE (or 0) as the range_lookup argument
- the closest match if you used TRUE (or 1) as the range_lookup argument
- Step 2 – the HLOOKUP function will return the value in the row_index_num you provided. Once it finds your lookup_value it will stop in that column within your table_array and then count as many rows down based on the number you provided in your row_index_num to return the result.
Note: Excel will generate an #N/A error if it doesn’t find your lookup_value. You need to make sure your lookup_value exists in the 1st row of your table_array and that it’s spelled exactly the same.
In our example, the result is $210,000:
- The function starts in cell B6 and says “Do you equal Calgary?”. Since B6 = “Year”, the answer is no.
- Go across to cell C6… “do you equal Calgary?”. The answer is no.
- Go across to cell D6… “do you equal Calgary?”. The answer is YES!
- Stay in column D.
- The function then counts 3 rows down (starting with our top row as row number 1)
- Return the value $210,000
Go back to Excel Tutorials.