HLOOKUP

Let's say Valentine's Day is coming up and you're the rare mix between a chick-magnet and a spreadsheet-nerd. Since you've got absolute game, you've been talking to a good amount of girls recently. With the holiday coming up, you want to pick a special someone to give flowers to and take out to dinner.

Your mixture of flirtatious skills and Excel wizardry has enabled you to create a spreadsheet of each of these girls you're talking to with each of their favorite flowers.

Difference between VLOOKUP & HLOOKUP

If we wanted to sort our love interests in a vertically orientated spreadsheet, it'd look like so:

If we wanted to sort our love interests in a horizontally orientated spreadsheet, it'd look like so:

The only difference between VLOOKUP and HLOOKUP is this: whether or not the list is oriented vertically or horizontally.

Coding a HLOOKUP function

Let's say we decide to ask Carly out on a date. Notice how Carly has an ID of 819...

...so let's place that in C5.

Now for our HLOOKUP template:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Our lookup_value is going to be C5, because that's where we placed the ID of Carly.

=HLOOKUP(C5, table_array, row_index_num, [range_lookup])

The table_array is going to be A1:F3, since that's where our horizontally-oriented table is contained.

=HLOOKUP(C5, A1:F3, row_index_num, [range_lookup])

For our row_index_num...

The row_index_num argument determines which row you want values to be shown for in the result.

Therefore, our row_index_num is going to be 3, since the 3rd row contains the flowers that each girl likes.

=HLOOKUP(C5, A1:F3, 3, [range_lookup])

[range_lookup], like with VLOOKUP, is going to be FALSE. We want to search for exact ID matches, not approximate.

=HLOOKUP(C5, A1:F3, 3, FALSE)

When we type this into Excel, we figure out that Carly like's Tulips!