HLOOKUP is kin to VLOOKUP. VLOOKUP looks up a value from a Vertical list and HLOOKUP looks up a value from a Horizontal list. Go figure. Here is a quick tutorial on the HLOOKUP function.
In this picture we have a list of People and their respective Sales and a Table showing the Bonus they get if they hit a certain target.
In Column C we want to show the bonus based on sales. We do this by grabbing the Sales, comparing it to the Bonus Target table, and returning the bonus amount using HLOOKUP.
Here is the HLOOKUP in layman’s syntax
Here is the HLOOKUP in layman’s syntax
=HLOOKUP(CellToLookup ,RangeToLookIn, WhichRowToReturn, ExactMatch?)
And now in practice:
1. In a blank cell that we would like to return a result from the list, type =HLOOKUP(
2. Click on the cell where there is a value to lookup (this will enter the cell in your formula)
3. Hit , (comma)
4. Click and drag to select the entire list to look in
5. Hit, (comma)
6. Type the numerical value of the row you would like to return (Don’t type the actual row number, this refers to the row to return in the data. In this example we return row 2 from our Bonus Target table.
7. Hit ,(comma)
8. Type “True” and hit enter
2. Click on the cell where there is a value to lookup (this will enter the cell in your formula)
3. Hit , (comma)
4. Click and drag to select the entire list to look in
5. Hit, (comma)
6. Type the numerical value of the row you would like to return (Don’t type the actual row number, this refers to the row to return in the data. In this example we return row 2 from our Bonus Target table.
7. Hit ,(comma)
8. Type “True” and hit enter
Sidenote: We type “True” to return an approximate answer. To return an exact match type “False”
Sidenote: If you notice the $ signs in the formula, this make the range we lookin absolute. We did this so we could autofill the formula down and our range to look in always stays the same.
The Following video demonstrate the concept very well:
No comments:
Post a Comment