VLOOKUP with condition
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VLOOKUP with condition

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

    Default

     
    Well I have one question:

    I have a master list (I call it that way) of ID, User name, Post Office (email world) - it is on sheet1. Now on my sheet2 I am trying to, no I am doing =VLOOKUP(A:A,sheet1!users,2,FALSE). So I match ID in A:A on sheet2 (Ohh yeah sheet2 has IDs listed - top to bottom) with users list from sheet1. It works... but there is a little twist to the story.... Multiple Post Offices allow the same ID (but unique within Post Office), therefore my VLOOKUP function picks first matching name from the top. Now in some cases IDs do not match the names... In this case I am asking administrators to enter Post Office name in sheet2 (B2). I am thinking that my formula should match ID only if B2 matches the users Post Office (C column in sheet1!users).
    I know I am sitting at front of very powerful application.. but functions and programming are not my strong side.

    Thanks for any suggestions and ideas.

    Desperate
    Andrew.

  2. #2
    Guest

    Default

    Sounds like you need to concatenate the ID and PostOffice so that you get unique ID & Post office combinations & then do your vlookup based on your new concatenated field.

  3. #3
    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

    On 2002-02-28 08:11, AnWeNeT wrote:
    Well I have one question:

    I have a master list (I call it that way) of ID, User name, Post Office (email world) - it is on sheet1. Now on my sheet2 I am trying to, no I am doing =VLOOKUP(A:A,sheet1!users,2,FALSE). So I match ID in A:A on sheet2 (Ohh yeah sheet2 has IDs listed - top to bottom) with users list from sheet1. It works... but there is a little twist to the story.... Multiple Post Offices allow the same ID (but unique within Post Office), therefore my VLOOKUP function picks first matching name from the top. Now in some cases IDs do not match the names... In this case I am asking administrators to enter Post Office name in sheet2 (B2). I am thinking that my formula should match ID only if B2 matches the users Post Office (C column in sheet1!users).
    I know I am sitting at front of very powerful application.. but functions and programming are not my strong side.

    Thanks for any suggestions and ideas.

    Desperate
    Andrew.
    Hi Andrew,

    My Mum works for Consignia and continually uses the word "desperate", so any fellow employees have my sympathies : can you post some concrete examples of your data or email me an example, I wouldn't mind taking a look at it, if it's not too late, for you.

    novulari@hotmail.com

    cheers
    Chris

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-28 10:41, Chris Davison wrote:
    On 2002-02-28 08:11, AnWeNeT wrote:
    Well I have one question:

    I have a master list (I call it that way) of ID, User name, Post Office (email world) - it is on sheet1. Now on my sheet2 I am trying to, no I am doing =VLOOKUP(A:A,sheet1!users,2,FALSE). So I match ID in A:A on sheet2 (Ohh yeah sheet2 has IDs listed - top to bottom) with users list from sheet1. It works... but there is a little twist to the story.... Multiple Post Offices allow the same ID (but unique within Post Office), therefore my VLOOKUP function picks first matching name from the top. Now in some cases IDs do not match the names... In this case I am asking administrators to enter Post Office name in sheet2 (B2). I am thinking that my formula should match ID only if B2 matches the users Post Office (C column in sheet1!users).
    I know I am sitting at front of very powerful application.. but functions and programming are not my strong side.

    Thanks for any suggestions and ideas.

    Desperate
    Andrew.
    Hi Andrew,

    My Mum works for Consignia and continually uses the word "desperate", so any fellow employees have my sympathies : can you post some concrete examples of your data or email me an example, I wouldn't mind taking a look at it, if it's not too late, for you.

    novulari@hotmail.com

    cheers
    Chris
    [img]/board/images/smiles/icon_smile.gif[/img]
    Andrew,

    I'm with Chris.

    It would help him and others if you would post a small sample of your data per sheet. If inclined to do so, select an empty cell, type =, select 10 rows worth of data, hit F9, and copy what you see and post it along with the range it occupies and expected results.

    Aladin

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I have figured it out but I got another challenge:
    I can use one of 2 formulas:
    =CONCATENATE(B9,MID(C2,1,LEN(C2)))
    or
    =CONCATENATE(B12,C2)
    and both of them do the job, but how do I prevent C2 from changing when I paste this into cell below? I want C2 to stay there.. I want to reference C2 from top to the bottom in column A.
    any ideas?
    Thanks

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-28 11:34, AnWeNeT wrote:
    I think I have figured it out but I got another challenge:
    I can use one of 2 formulas:
    =CONCATENATE(B9,MID(C2,1,LEN(C2)))
    or
    =CONCATENATE(B12,C2)
    and both of them do the job, but how do I prevent C2 from changing when I paste this into cell below? I want C2 to stay there.. I want to reference C2 from top to the bottom in column A.
    any ideas?
    Thanks
    Freeze it: $C$2.

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

    Default

      
    Thanks a lot. It worked... I can go home.

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