Results 1 to 6 of 6

Thread: Complex VLookup?

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex VLookup?

    Hi there,

    Sorry to bother you but I was wondering if any Excel experts could give some guidance on a VLookup question?

    I am trying to look-up two combined records. I have a sheet with Entity 1, Entity 2, Entity 3 as columns, then as rows (headers) I have departments (Accounts, HR, Admin). I want to be be able to have a drop down in a preview sheet with the entity and department and then for Excel to lookup and populate the person in each dept?

    This is what I am trying to achieve (below) - in my overview sheet if I have "Entity A" with "Admin" I would like to see that "Jane Bloggs" is the contact - similarly if I had "Entity A" with "Accounts" it would show "Joe Bloggs"

    I have the department and entity as drop down to assist with the lookup command. I tried unsuccessfully tried to use the concatenate formula but it just got very messy and didn't work.

    Thanks a million for your time reading this!!

    Darren.

    Accounts HR Admin
    Entity A Joe Bloggs Jane Bloggs
    Entity B
    Entity C
    Vlookup 1 Vlookup 2 Result (=Entity & Admin)
    Entity Department Jane Bloggs

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Complex VLookup?

    Hi & welcome to MrExcel.
    How about

    ABCD
    1AccountsHRAdmin
    2Entity AJoe BloggsJane Bloggs
    3Entity B
    4Entity C
    5
    6
    7
    8Entity AAdminJane Bloggs

    Dont



    Worksheet Formulas
    CellFormula
    C8=INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(B8,B1:D1,0))

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    1,916
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex VLookup?

    With your dropdowns in G7 and H7, try =VLOOKUP(G7,A1:D4,MATCH(H7,1:1,))
    Last edited by BenMiller; Sep 11th, 2019 at 03:21 PM.
    "I'm not a perfectionist; I'm just perfect."

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex VLookup?

    Hi Fluff!

    Thanks a million for your quick solution! I tried this and it works perfectly. I don't seem to be able to press "Thanks" but a very big thank-you!

    Have a nice evening!

    Darren.


    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel.
    How about

    A B C D
    1 Accounts HR Admin
    2 Entity A Joe Bloggs Jane Bloggs
    3 Entity B
    4 Entity C
    5
    6
    7
    8 Entity A Admin Jane Bloggs
    Dont

    Worksheet Formulas
    Cell Formula
    C8 =INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(B8,B1:D1,0))

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex VLookup?

    Thanks BenMiller!

    I appreciate you getting back with your solution so soon! I have played around with your formula and it works perfectly too.

    I feel like it was such a basic question but obviously not for the less well experienced!

    Great to have expert advice!

    Take it easy, Darren.

    Quote Originally Posted by BenMiller View Post
    With your dropdowns in G7 and H7, try =VLOOKUP(G7,A1:D4,MATCH(H7,1:1,))

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Complex VLookup?

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •