Using the HLOOKUP function on a Mac

August 11, 2016

0 comments



We’re a team of problem solvers, world travelers, idea guys, and doers, working to make using Excel on a Mac a better experience.

That’s enough about us -- whether you are new to Excel for Mac or an experienced user, you probably want to spend less time manipulating data and more time thinking. The Excel Skin will help you do that. If you are a PC user hesitant about switching to Mac because you don’t want to give up your customized menus and shortcuts, well, the Excel Skin just might be the myth-buster you need. Excel for Mac is just as capable as it is for the PC. And with the Excel Skin, you’ll relearn your old shortcuts in no time.

The Excel Skin makes it easy to be as proficient and efficient in Excel for Mac as on any other platform. Powerful shortcuts are presented intuitively on an elegant silicone keyboard skin. Shortcut and modifier keys are printed by color and heat fused onto our high quality silicone to ensure durability.

The Excel Skin a small product that will make a big difference in your life.

 

So we’ve covered how to execute a VLOOKUP function, but what about a HLOOKUP?  HLOOKUP is the same function as VLOOKUP, only horizontal.  Note, the letter in front of “LOOKUP” indicates whether the search will be a vertical or horizontal search.

With HLOOKUP’s we are working with rows instead of columns.  So when would you use this function over a VLOOKUP? Follow along to find out.

The syntax of the function is: HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Definitions of each argument:

lookup_value - The value that you want to search for, in the first row of the supplied data array.

table_array - The data array or table, containing the data to be searched in the top row, and the return values in any other row.

row_index_num - The row number, within the supplied table_array, that you want the corresponding value to be returned from.

[range_lookup] - An optional logical argument, which can be set to TRUE or FALSE, meaning:

TRUE - if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value. Note: If [range_lookup] is set to TRUE, the top row of the table_array must be in ascending order).

FALSE - if the function cannot find an exact match to the supplied lookup_value, it should return an error.

Now, lets put this into practice.

I have a spreadsheet that shows what each person spent on 3 specific categories.  I want to pull the Entertainment category for each person and list those out with my HLOOKUP function:

 

And with the HLOOKUP formulas revealed (=HLOOKUP(A10,A2:E5,4,FALSE): 

 

In the above example, the HLOOKUP function searches through the top row of the table_array (the range A2-E5), to find a match for the lookup_value (the name in A10-A13). When the the name is found, the function returns the corresponding value from the 4th row of the table_array.  We added FALSE so the name matching has to be exact.

 

As always, feel free to reach out with any questions!  

 



Leave a comment

Comments will be approved before showing up.