Results 1 to 4 of 4

Thread: Vlookup OR Index/Match function with multiple criteria

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup OR Index/Match function with multiple criteria

    Employee Name Employee ID Position # Position Status
    Employee A 111111111 111111 A
    Employee A 111111111 777777 W
    Employee B 222222222 222222 A
    Employee C 333333333 333333 A
    Employee D 444444444 444444 W
    Employee D 444444444 555555 A

    Hello there,

    I'm attempting to setup a Index match or Vlookup formula, but having issues when doing so with mutiple criteria. Hoping for help as I'm confused with other threads and email searches.

    Currently my formula is setup to look at employee ID and pull job data relating to a position from the employee. The issue though is that some employees here have multiple positions where they may not necessary be active. I need a formula that allow me to pull employee ID- and then only look at the active (A) position data. The table below is how information looks. Employee A and D for example have the same name/ID, but may have 2 different positions. Thank you for any assistance
    Last edited by ld1414; Sep 13th, 2019 at 02:00 PM. Reason: Making Table easier to read

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

    Default Re: Vlookup OR Index/Match function with multiple criteria

    How about

    ABCDEF
    1Employee NameEmployee IDPosition #Position Status444444444
    2Employee A111111111111111A555555
    3Employee A111111111777777W
    4Employee B222222222222222A
    5Employee C333333333333333A
    6Employee D444444444444444W
    7Employee D444444444555555A

    Sheet2



    Worksheet Formulas
    CellFormula
    F2=INDEX(C2:C7,MATCH(F1&"|A",INDEX(B2:B7&"|"&D2:D7,0),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
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup OR Index/Match function with multiple criteria

    Quote Originally Posted by Fluff View Post
    How about

    A B C D E F
    1 Employee Name Employee ID Position # Position Status 444444444
    2 Employee A 111111111 111111 A 555555
    3 Employee A 111111111 777777 W
    4 Employee B 222222222 222222 A
    5 Employee C 333333333 333333 A
    6 Employee D 444444444 444444 W
    7 Employee D 444444444 555555 A
    Sheet2

    Worksheet Formulas
    Cell Formula
    F2 =INDEX(C2:C7,MATCH(F1&"|A",INDEX(B2:B7&"|"&D2:D7,0),0))
    Perfection! Thank you sir. I sadly still don't quite understand the formula, but it works nonetheless.

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

    Default Re: Vlookup OR Index/Match function with multiple criteria

    It's concatenating the value in F1 with |A and concatenating the Values in colb with a | and the values in col d.
    So you would end up looking for 444444444|A in a list of values like

    H
    2111111111|A
    3111111111|W
    4222222222|A
    5333333333|A
    6444444444|W
    7444444444|A

    Sheet2



    - 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
  •