hlookup

tinad

New Member
Joined
Apr 7, 2002
Messages
2
Can anyone give me a step by step tutorial on how to use hlookup and vlookup
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I'm not trying to be cheeky, so I hope this doesn't sound like I am.

Have you tried looking at Excel Help for VLookup and HLookup? There are a couple of decent examples on them in the Help file.

If there's something you don't understand from those examples, then this is a good place to ask.

HTH
 
Upvote 0
Yes, and I still don't quite understand it.

No, I didn't think you were being rude, by no means..

Thanks anyway,
Tina
 
Upvote 0
No problems. I sent that first post from work, so I can actually take a bit of time on it now. (plus I'm on my second alcoholic beverage for the evening, long day)

OK, I'll explain VLookup first, because I've used that one. HLookup is very similar, but we'll ignore it for now. (Note, I usually only do VBA so if I'm slightly wrong, someone will correct me. Trust me, I will be corrected, I'm not naming any names though :) )

OK say you create a table in Excel that looks like this:<pre><U> A B C D E F</U>
1 | A 1 a
2 | B 2 b
3 | C 3 c
4 | D 4 d
5 | E 5 e</pre>

(This is a pathetic representation of an Excel spreadsheet with data in A1:C5)

Now, in cell F1 we're going to enter this formula:

=VLOOKUP(E1,A1:C5, 2)

In cell E1 type in A, B, C, D or E. The value in F1 will now be 1,2,3,4 or 5 depending what value is in E1.

How it works:

In this VLOOKUP formula there are three things

1. E1 - This is the cell that we are using to hold our text that we are referencing. You could just as easily change E1 for "A" (including quotes) to return the value of 1.

2. A1:C5 - we are telling VLOOKUP that this is the range that contains the data table.

3. 2 - this is telling VLOOKUP to get the value from column 2 that is in the same row as the letter we are telling it to look for. e.g. "A".

So basically VLOOKUP will look in the first column of a table (e.g. column A in range A1:C5) and try to find the text that we specify (e.g. "A"). We tell VLOOKUP which column we'd like the data from if it finds a match (in this case column 2 or column B).

To further illustrate, change the "2" in F1's formula to be a 3. The values that will be returned will be a,b,c,d or e, dependent upon the value in cell E1.

I hope this makes sense.

Now, for HLOOKUP. HLOOKUP does exactly the same thing as VLOOKUP except that HLOOKUP looks in the first row of the table and NOT the first column. You tell HLOOKUP what row you would like data from as opposed to which column.

If you need any further explanation, just repost.
This message was edited by Mark O'Brien on 2002-04-08 18:35
This message was edited by Mark O'Brien on 2002-04-08 18:36
This message was edited by Mark O'Brien on 2002-04-08 21:54
 
Upvote 0
Good one Mark,

this style beats the pants off the official help files

I often think of the "V" in (V)LOOKUP to mean that it looks at the first column and slides down (V)ertically before it finds a match

ditto, the "H" in (H)LOOKUP to mean that it looks at the first row and slides along (H)orizontally before it finds a match

At an advanced Excel course a year or so ago, the teacher said "well, we'll skip VLOOKUP and HLOOKUP cos I can't see what use they can be and anyway, I can never remember which way round they were...."

dear me....!!
 
Upvote 0
Yeah, I omitted the "H" and "V" for vertical and horizontally, irrespective of order. Funnily enough I was only made aware of HLOOKUP yesterday morning before this post.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top