All language subtitles for 3. Looking Up Information Horizontally Using HLOOKUP

Both of the Vila Cut formulas that we've looked at so far in this section have been using data that

runs vertically down the page, hence why it's called the look up.

So in this lesson, I want to show you an example of h lookup where our data is running horizontally

across the page instead.

Now what do I mean by that?

Well, on this worksheet, I have an example of data that runs vertically down the page.

We have our column headings at the top and then our rows of data underneath.

And this is by far the most common way to display data.

A less common way is to have your column headings in the rows as opposed to in the columns, so that

your data runs horizontally across the page.

And whilst this is definitely not as common, you need to be able to have the tools to be able to deal

with that scenario and still perform your lookups.

So what we're going to do, first of all, is we're going to grab this data and we're going to flip

it the other way round.

And this is also a good opportunity for me to show you a little command you can use if you need to switch

data around for any specific reason.

So I'm going to click my data table control I to select everything control C to copy, and then I'm

going to go across to my H Lookup worksheet and I'm going to paste this data onto the worksheet, but

we're going to paste it running horizontally as opposed to vertically.

So what we can do here is on the Home tab up to paste, click the lower half and go to Paste Special.

And if we choose the transpose command that will basically flip your data around the other way when

I click on OK, you can see what that's done.

I now have my column headings in the rows and my data runs horizontally across the page.

And if I zoom out a little bit because I do want to do a little bit of resizing, so we don't have those

hash symbols in some of the cells.

It's double click to resize all of those and then zoom back out again.

Perfect.

Now, as I said, this is by far not my favorite way of displaying data.

It really does look quite messy, but you will come across datasets throughout your Excel career, which

will be structured in this way.

So if we have our data this way around, how can we do a look up?

Well, the simple answer is pretty much in exactly the same way, just using the H lookup function as

opposed to V look up.

So what I want to do up here is I want to be able to type in a movie name.

So let's just start with La La Land and I want it to return the year, what it's rated and the genre.

So the first thing I'm going to do is I'm going to create a named range for my dataset set down here.

Control A to select all up to that name box.

I'm going to say movie list and enter.

So now if I want to return the year, I'm going to use each look up.

We have exactly the same arguments.

There is one minor difference in here which I'll speak about more when we get to.

Our first argument is the look up value.

Well, we want to look up the film, so be five.

Table Array Where are we looking it up?

Well, we've created a named range.

I can press at three and select my movie list.

This time it's asking for a row index number as opposed to a column index number.

So instead of counting across columns from left to right, we simply need to count down rows until we

get to the one that we want to return.

So we want to return the year.

In this example, and that is row number two.

And then we get to use an approximate or exact match.

Well, I want to exactly match the movie name in the table, so we're going to have a false argument

on the end here.

Close the bracket and hit enter.

And it's telling me that La La Land was released in 2016, and I can say that yes, it was.

Let's do it again.

So equals h.

Look up, look up.

Value is the movie f three to select a table array.

Row number this time is row number three because we want to return what it's rated and we're doing an

exact match.

So I'm going to put a zero on the end to close the bracket.

Enter.

And there we go.

Let's complete this set.

This time I'm going to use the functions dialog box and we're going to search for each look up.

There it is.

Double click to open look up value movie name.

Table Array Let's press three.

Select our movie list row index number.

Or this time we're looking for the genre.

So that is row number one two three four five row number six and our range look up.

We're doing a exact match, so it's a false.

Let's click on.

OK, and there we go.

And if I was to change this film names, let's type in Jackie, I should find that all of these results

updates.

Let's check our table.

There is Jackie.

Everything is looking good.

So that's really the only difference between Velika and H.

Look up.

It's all about how your data is structured and whether you have those column headings in the rows or

the columns.