What is hlookup used for

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data. Looks up "Axles" in row 1, and returns the value from row 2 that's in the same column column A.

Looks up "Bearings" in row 1, and returns the value from row 3 that's in the same column column B. Looks up "B" in row 1, and returns the value from row 3 that's in the same column. Because an exact match for "B" is not found, the largest value in row 1 that is less than "B" is used: "Axles," in column A. Looks up "Bolts" in row 1, and returns the value from row 4 that's in the same column column C. Looks up the number 3 in the three-row array constant, and returns the value from row 2 in the same in this case, third column.

There are three rows of values in the array constant, each row separated by a semicolon ;. Let's throw in one more complication.

What happens if your sheet name contains spaces? If there are spaces in the sheet name, you will need to change the formula further. Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:G3, we would need to modify our formula as follows:.

Now it is important for us to mention one more mistake that is commonly made. This will return the right answer, but what happens when you copy the formula to another cell? The table range will be adjusted by Excel and change relative to where you paste the new formula. Let's explain further Since your table is found in the range A1:G3 and not B2:H4, your formula would return erroneous results in cell K2.

Share Share Tweet Subscribe. About Author: Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. Newer Post Older Post Home. Subscribe to: Post Comments Atom. HLOOKUP is a very useful function for creating horizontal lookups, but as most of the tables that we deal with are vertical hence this function is not very popular.

The task of the HLOOKUP function is to search for a value in the topmost row of a table, and then return a corresponding value in the same column from a row you specify. In this example, we have an Element Table as shown below and our task is to find the Atomic Mass of Boron. Using the above element table find the Melting Point of an element whose Atomic Mass is 15 or slightly less than it. And, hence the result is Explanation: In this code, we are using multiple Horizontal LookUp formulas to fetch the marks of the student in different subjects.

If the student entered by the user is not in the table, the code pops out a message saying "Student Not found in the records!