hlookup

[tinad]

Can anyone give me a step by step tutorial on how to use hlookup and vlookup

[Mark O'Brien]

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

[tinad]

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

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

Thanks anyway,
Tina

[Mark O'Brien]

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:

       A    B    C    D    E    F    

 1 |   A    1    a

 2 |   B    2    b

 3 |   C    3    c

 4 |   D    4    d

 5 |   E    5    e

(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 ]

[kinkyparamour]

Mark,

That was a very good explanation. I use vlookup a lot and now i understand why...;o)

Denny

[Chris Davison]

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....!!

[Mark O'Brien]

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.