HLOOKUP in Excel - it's that easy
You need the HLOOKUP in Excel if you want to get information from a horizontal table. We show you how to use the function correctly.
How the HLOOKUP works in Excel
The HLOOKUP in Excel searches for a value in a predefined table. As a result you get what is in the column with this value in a predefined row. That sounds more complicated than it is. You can find step-by-step instructions in the picture gallery. In theory, the HLOOKUP looks like this: "= HLOOKUP (search criterion; matrix; row index; true / false)"
- The search criterion is the number or word you are looking for. If you want to search for a term, you have to put it in quotes. If the value is in another cell, select the cell in question instead of entering anything.
- The matrix is the part of your Excel spreadsheet that contains the data you want to use. Highlight the range as you type the formula. Alternatively, you can insert the matrix manually by typing in the upper left cell name, then a colon and then the lower right cell name. An example: The matrix is contained in cells A1, B1, A2, B2, A3, B3. So type "A1: B3".
- The row index is the number of the row in which the result is. If the row with the desired value is the third row of the matrix, the row index is 3.
- True or False indicates whether the value you are looking for may differ from the data in the matrix or not. "True" gives a result even if there is an approximate match, "False" does not.
In the next practical tip, you will read how to recognize which version of Office is running on your computer.
Latest videos
The initial situation: You have a price list and article numbers of a purchase and want to use the article numbers to find out the name and price of the goods.
Go to the first field in the name column and start the HLOOKUP there. The search criterion (article number) is given in cell "B8". In this example, the matrix (the price list) is on B2: I4. So that you can later apply the formula to the fields below, press [F4] after entering to "absolutely set" the matrix. The $ signs appear in the name of the matrix. If you don't, the matrix will slide down when the formula is applied to the rows below. You still need the right name for the article number, which is why the row index is 2, because the names are in the second row of the matrix. Finally, type in "wrong" to be able to work with exact article numbers. If a number is not in the list, an error message should appear and a similar article number should not be used.
To apply this formula to the rows below, move the mouse over the black corner at the bottom right of the cell marker. When the pointer turns into a cross with no arrows, you can drag the formula down.
To find out the price of the products, enter the second HLOOKUP. This works exactly like the first one, except that the row index is now "3" because the price is in the third row of the matrix.
Drag the completed HLOOKUP back to the other lines below. Now all you have to do is multiply the quantity by the price (= D8 * E8 and then pull it down) and calculate the sum (= sum (F8: F11)).
