Sometime back we came up with a post on
VLookUp In MS Excel 2007. Now we will move on and explain how to do a HLookUp in MS Excel 2007.
Before continuing further, lets see what makes VLookUp different from HLookUp.
VLookUp vs HLookUp:
VLookUp is the term used to refer vertical lookup whereas in HLookUp stands for horizontal lookup. This means when we do a VLookUp, the values are searched vertically whereas when we do a HLookUp, the values are searched horizontally.
How to do a Hlookup?
In the figure below you can see that we have data available in "values" and "colour" for different categories.
We would require extracting data for the output.
As the data is available in horizontal format, in order to extract it in vertical format in the output header we need to use Hlookup function.
Lookup_value : This is the value on which you want to do a Hlookup.
Table_array: This is the area from which the data has to be extracted.
Row_index_num: This denotes from which row is the data to be extracted.
Please ensure that you always lock the table array cells. This will help you to copy paste the formula easily because the table array is the fixed place from where we want to extract the data for all hlookup values.
The above formula once done will extract the data for the Hlookup value (G)
Now in order to extract the data for colour of the Hlookup value, do the below. This is similar to what we did for values column.
And once done, just copy paste the formula of both "values" and "colour" below in the range.
And that’s it. You are done with Hlookup.
We would require extracting data for the output.
As the data is available in horizontal format, in order to extract it in vertical format in the output header we need to use Hlookup function.
Lookup_value : This is the value on which you want to do a Hlookup.
Table_array: This is the area from which the data has to be extracted.
Row_index_num: This denotes from which row is the data to be extracted.
Please ensure that you always lock the table array cells. This will help you to copy paste the formula easily because the table array is the fixed place from where we want to extract the data for all hlookup values.
The above formula once done will extract the data for the Hlookup value (G)
Now in order to extract the data for colour of the Hlookup value, do the below. This is similar to what we did for values column.
And once done, just copy paste the formula of both "values" and "colour" below in the range.
And that’s it. You are done with Hlookup.
