Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.