Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: hlookup

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Thanks anyway,
    Tina

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark,

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

    Denny

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •