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

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.