VLOOKUP
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VLOOKUP

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

    Default

     
    If I have a value in one file (A1) that needs to match to a value in another file which is in two columns (A1:B1), how do I get a return in column C. Example:
    File 1
    A
    Cat
    Dog
    Pig
    Horse

    File 2
    A B
    Cat Meow
    Frog Croak
    Dog Woof
    Chick Peep
    Horse Neigh
    Duck Quack
    Pig Oink

    File 1 should now be:
    A B
    Cat Meow
    Dog Woof
    Horse Neigh
    Pig Oink


    [ This Message was edited by: Alexander on 2002-04-16 10:15 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    What problem are you having with Vlookup?
    What result do you get?

    Could you show your formula?

    If the information is not sorted and you want an exact match, add the 4th parameter
    - False.



    See Help for Syntax.

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

    Default

    Is there a way I can send you a copy of the files so that you may look at it and tell me how to write the formula? My email is bettillee@msn.com.

  4. #4
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 10:05, Alexander wrote:
    If I have a value in one file (A1) that needs to match to a value in another file which is in two columns (A1:B1), how do I get a return in column C. Example:
    File 1
    A
    Cat
    Dog
    Pig
    Horse

    File 2
    A B
    Cat Meow
    Frog Croak
    Dog Woof
    Chick Peep
    Horse Neigh
    Duck Quack
    Pig Oink

    File 1 should now be:
    A B
    Cat Meow
    Dog Woof
    Horse Neigh
    Pig Oink


    [ This Message was edited by: Alexander on 2002-04-16 10:15 ]
    Assuming file 1 and 2 are worksheets

    In B1 of file 1 enter
    =VLOOKUP(A1,file2!$A$1:$B$7,2,1)

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there. Brian's explanation looks like it will work for you. If not, or if you're having difficulty understanding the vlookup formula, you can check this out:

    http://www.thewordexpert.com/excel.h...DreadedVlookup

    Hope it helps!
    ~Anne Troy

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    or try Brian's formula
    revised slightly

    In B1 of file 1 enter
    =VLOOKUP(A1,file2!$A$1:$B$7,2,0)

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

    Default

    This works fine, except file 1 has 2000 lines and file 2 will vary, from 100 to 1000. It is not matching correctly.

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

    Default

    Never mind. I was wrong. It works great and thank you all!

  9. #9
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-16 11:00, Alexander wrote:
    This works fine, except file 1 has 2000 lines and file 2 will vary, from 100 to 1000. It is not matching correctly.
    If column A in file 2 has data that is repeated, it will match the first data that is repeated. Not sure what you mean by file 2 will vary, but you can name the range in file 2 i.e =vlookup(a1,range name,2,0)

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
  •  

 

 
DMCA.com