Results 1 to 6 of 6

Thread: multi criteria multi result lookup

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default multi criteria multi result lookup

    trying to adapt https://www.get-digital-help.com/mat...rows-in-excel/ to work for me. my version of the formula is:

    =INDEX($AK$7:$AK$15, SMALL(IF(COUNTIF($AG$5,$AJ$7:$AJ$15)*COUNTIF($AG$6,$AJ$7:$AJ$15)*COUNTIF($AG$7,$AJ$7:$AJ$15), ROW($AJ$7:$AL$15)-MIN(ROW($AJ$7:$AL$15))+1), ROW(A7)), COLUMN(A7))

    AG5 52MFD
    AG6 444B3 AJ AK AL
    AG7 52MF 7 444B1 John Done 10.00 #NUM!
    8 444B1 Paul Book A/L
    9 52MFD Sam Johnson 9.00
    10 444B1 Andrew Bloke A/L
    11 52MF Eric Fan 10.00
    12 444B2 Paul Peters 8.50
    13 444B3 John Sands 11.50
    14 52MFD Guy Noob 9.00
    15 444B1 Old Mate 10.00
    I want to list each name for the three codes at left. what am I doing wrong?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: multi criteria multi result lookup

    Copy AM7 down.
    Sheet1

    AGAHAIAJAKALAM
    552MFD
    6444B3
    752MF 7444B1John Done10Sam Johnson
    8 8444B1Paul BookA/LEric Fan
    9 952MFDSam Johnson9John Sands
    10 10444B1Andrew BlokeA/LGuy Noob
    11 1152MFEric Fan10
    12 12444B2Paul Peters8.5
    13 13444B3John Sands11.5
    14 1452MFDGuy Noob9
    15 15444B1Old Mate10

    Spreadsheet Formulas
    CellFormula
    AM7{=IFERROR(INDEX($AK$7:$AK$15,SMALL(IF(COUNTIF($AG$5,$AJ$7:$AJ$15)+COUNTIF($AG$6,$AJ$7:$AJ$15)+COUNTIF($AG$7,$AJ$7:$AJ$15),ROW($AJ$7:$AL$15)-MIN(ROW($AJ$7:$AL$15))+1),ROWS($A$7:$A7))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: multi criteria multi result lookup

    so, I had the rows at the end wrong and my countif around the wrong way. why in these formulas does the countif layout differ to the normal coumtif? In this context, the criteria is placed before the range.

    the fix above works perfectly; thank you. can it be further tweaked to list only those who did not have hours? for example, say eric fan and john sands had no hours this week. how would I adjust the formula to return only those two names?

    I have just tried to add in another countif expression looking for null (blank) hours but it seems to skip the 4th argument, any ideas?
    Last edited by ajm; Oct 20th, 2019 at 02:34 AM.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: multi criteria multi result lookup

    Quote Originally Posted by ajm View Post
    so, I had the rows at the end wrong and my countif around the wrong way. why in these formulas does the countif layout differ to the normal coumtif? In this context, the criteria is placed before the range.

    the fix above works perfectly; thank you. can it be further tweaked to list only those who did not have hours? for example, say eric fan and john sands had no hours this week. how would I adjust the formula to return only those two names?

    I have just tried to add in another countif expression looking for null (blank) hours but it seems to skip the 4th argument, any ideas?
    You are welcome - thanks for the reply. Just FYI, your formula also had the Countif's requiring an AND condition (Countif1*Countif2*Countif3) whereas it should be an OR condition (Countif1+Countif2+Countif3).

    As for your new question, assuming that "A/L" in col AL indicates no hours this week, the formula can be adjusted as below.
    Sheet1

    AGAHAIAJAKALAM
    552MFD
    6444B3
    752MF 7444B1John Done10Eric Fan
    8 8444B1Paul BookA/LJohn Sands
    9 952MFDSam Johnson9
    10 10444B1Andrew BlokeA/L
    11 1152MFEric FanA/L
    12 12444B2Paul Peters8.5
    13 13444B3John SandsA/L
    14 1452MFDGuy Noob9
    15 15444B1Old Mate10

    Spreadsheet Formulas
    CellFormula
    AM7{=IFERROR(INDEX($AK$7:$AK$15,SMALL(IF(($AL$7:$AL$15="A/L")*(COUNTIF($AG$5,$AJ$7:$AJ$15)+COUNTIF($AG$6,$AJ$7:$AJ$15)+COUNTIF($AG$7,$AJ$7:$AJ$15)),ROW($AJ$7:$AL$15)-MIN(ROW($AJ$7:$AL$15))+1),ROWS($A$7:$A7))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: multi criteria multi result lookup

    thanks very much for your help Joe.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: multi criteria multi result lookup

    Quote Originally Posted by ajm View Post
    thanks very much for your help Joe.
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

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
  •