Tagged as: hlookup, index match, lookups, tutorials, vlookup To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon. VLOOKUP and HLOOKUP are two of the most popular formulas in Excel and using them together is one of the first formula combinations that people learn.
![]()
In this video, we'll look at how to use HLOOKUP. HLOOKUP works just like VLOOKUP, but instead of getting the value of an item from a certain column in a table, HLOOKUP gets a value from a certain row in a table.Let's take a look.Here's an the VLOOKUP commission example we've looked at previously. Let me quickly run through that example again to recap.To lookup the correct commission value in this table with VLOOKUP, we give VLOOKUP the value to lookup and the table array to use. In this case, I won't use a named range, but I will convert the table to an absolute reference so I can copy the formula down.Next, we give VLOOKUP the column number from which to get the commission rate, in this case 2. Finally, we need to specify an exact or approximate match. We definitely want an approximate match.Now when I copy this down, we get the correct commission rates for each salesperson in the list.Now let's perform exactly the same lookup using HLOOKUP.
To do this, I need to first transpose the table from a vertical layout to a horizontal layout. This is easy to do with Paste Special transpose feature.I just copy the table, bring up Paste Special, and select Transpose.Now we have a horizontal table and can build the HLOOKUP formulas.Again, HLOOKUP has identical functionality to VLOOKUP.I need to supply the value to look up and the table array (which I'll again make an absolute reference). Now I need to provide a row index. In our table, the commission rates are in the 2nd row, so I need to use 2. Finally, I need to make sure HLOOKUP is using approximate match.When I press enter and copy the formulas down, HLOOKUP gives us the same commission rates we got using VLOOKUP.Just like VLOOKUP, HLOOKUP is fully dynamic and will return the current information in the lookup table.
I am new to Numbers, but pretty skilled in Excel. This is also my first time posting a question to this forum so please let me know if you'd like me to provide more detail.
I am trying to find an Excel vLookup equivalent in Numbers that'll accomplish the following:
Find an exact match of a string of characters from Column A in a different tab called 'Vlookup' and return the string of characters from the corresponding Column B from that same 'Vlookup' tab. My Excel formula was this: VLOOKUP(C133, Vlookup!$A$2:$B$267,2, FALSE).
- Vlookup / Hlookup in Numbers seem to require a numeric value return - I need to return a string.
![]()
- Lookup in Numbers almost works but doesn't seem to have the option to specify an exact match - I need to find an exact match. It's incorrectly pulling the closest match instead of returning error when a match is not found.
Thanks!
MacBook Air (13-inch, Early 2015), OS X Yosemite 10.10.4
![]()
Posted on
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |