As discussed at the beginning of this book Hlookup stands for Horizontal lookup. It is same like Vlookup in every way but it will lookup horizontally instead of vertically. Because in Excel the data is arranged vertically this function is not that popular or of little use in Excel when compare to Vlookup. We will look into the syntax. Syntax: HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) We will go through an example and explain each parameter separately. It is the same parameters we have used for Vlookup. Example: Open the file Chapter 4.1 - Hlookup.xlsx Here we are using the same example we have used for Vlookup. For this, I have arranged that data horizontally like this in the image. [example] Now enter the formula =HLOOKUP(B1,$F$5:$H$6,2,0) in B2 column. lookup_value - Here the lookup value is the name George, So first we have entered the cell reference containing the name George which is in B1. table_array - This is where the persons who got the bonus and the bonus amount is entered. So we have to select the entire cells from F5:H6 and put dollar sign around them to lock the cells. Keep in mind the first row should be the name of the person we are comparing from the first parameter. row_index_num - this index number will decide which data to fetch. Since we have selected two rows in the second parameter, we must give 2 to get the second row detail which is bonus. range_lookup - Since we want an exact match, we have to put False or 0. Once you complete the first formula, you can copy the entire formula across the column to get the result against each name.

01 Hlookup as an array formula

Example: Open the file Chapter 4.2 - HlookupArray.xlsx Here is another example where we are using the array formula. We have six students in the first row and their score in each subject is mentioned in the next rows. Now if you want to retrieve multiple values from a single Hlookup formula you can use array formula like this. In other words, we are transposing the score horizontally. [example] First, you have to select the rows from C9:G9 and then type the Hlookup formula =HLOOKUP($C$8,$B$1:$G$6,{2,3,4,5,6},0). We are retrieving the score of all the students in five subjects so we must select five columns. Once you have selected five columns, you can enter the formula. The first parameter is C8 cells where we have mentioned the student's name. The second parameter we provide the cell range having data. The third parameter you have to put all the index number in a curly bracket to retrieve the score and the fourth parameter as false or 0 for Exact match. Now instead of Enter key, you have to press Ctrl + Shift + Enter to get the array formula. Now all the scores of the student will be displayed horizontally with this single lookup. You will see two curly brackets at beginning and end if the formula gets converted to array formula.

02 Hlookup with auto sum

Example: Open the file Chapter 4.3 - HlookupArraySum.xlsx In the above example, we have retrieved individual score for each student. Now, what if we want to get the total of the student's scores with a single formula. Then you have to use Sum formula along with Hlookup. Refer the image for you to understand. [example] Enter this formula in C9 cell =SUM(HLOOKUP(C8,B1:G6,{2,3,4,5,6},0)) and press Ctrl + Shift + Enter to convert it to array formula. You will see two curly brackets once the formula converted to array formula. Here I had added Sum function before Hlookup and converted the formula to array formula. So it will sum all the cells specified in the second parameter based on the index number specified in the third parameter of the Hlookup formula. The third parameter you can specify in the curly brackets. You can also mention the row numbers of which you want the total. For example, you can mention the third parameter like this {2,4,6} or {3,5} depends upon the total of the rows you want. Since Hlookup in every way is same as Vlookup I'am not going into further examples. All the example files done using Vlookup in this book can be done with Hlookup if data is arranged horizontally.

× DATE: Thu, Feb 06, 2020
× TIME: 9:38:44 pm
on Twitter or sign-up for my monthly newsletter: