Formula/VLOOKUP for multiple 'greater than' criteria to return multiple results

  • Hi All. I could really use some help :( Essentially, I have the below data:


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    3

    [/td]


    [td]

    4

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    ID

    [/td]


    [td]

    Fruit

    [/td]


    [td]

    # of Spots

    [/td]


    [td]

    State from

    [/td]


    [td]

    Days since Harvested

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    1

    [/td]


    [td]

    Apple

    [/td]


    [td]

    5

    [/td]


    [td]

    NY

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    2

    [/td]


    [td]

    Orange

    [/td]


    [td]

    3

    [/td]


    [td]

    NJ

    [/td]


    [td]

    40

    [/td]


    [/tr]


    [tr]


    [td]

    D

    [/td]


    [td]

    3

    [/td]


    [td]

    Banana

    [/td]


    [td]

    6

    [/td]


    [td]

    NJ

    [/td]


    [td]

    32

    [/td]


    [/tr]


    [tr]


    [td]

    E

    [/td]


    [td]

    4

    [/td]


    [td]

    Apple

    [/td]


    [td]

    11

    [/td]


    [td]

    AK

    [/td]


    [td]

    40

    [/td]


    [/tr]


    [tr]


    [td]

    F

    [/td]


    [td]

    5

    [/td]


    [td]

    Orange

    [/td]


    [td]

    12

    [/td]


    [td]

    NY

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    G

    [/td]


    [td]

    6

    [/td]


    [td]

    Banana

    [/td]


    [td]

    9

    [/td]


    [td]

    AK

    [/td]


    [td]

    60

    [/td]


    [/tr]


    [tr]


    [td]

    H

    [/td]


    [td]

    7

    [/td]


    [td]

    Apple

    [/td]


    [td]

    15

    [/td]


    [td]

    NJ

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [/TABLE]


    However, right now I am only interested in specific fruits that have >10 spots AND have >30 days since harvested. So, if I list
    [TABLE="width: 50"]

    [tr]


    [td][/td]


    [td]

    6

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    Apple

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    Orange

    [/td]


    [/tr]


    [/TABLE]


    I am trying to create a formula/vlookup/array that will generate a result of
    [TABLE="width: 50"]

    [tr]


    [td][/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    4

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [/TABLE]


    I have been trying to create a formula using SMALL, IF & ROW, but to no avail. Any help would be greatly appreciated. Thanks.

  • Re: Formula/VLOOKUP for multiple 'greater than' criteria to return multiple results


    you have the column and row references in your samples transposed...


    but assuming you are entering your inputs in F2:F3, then in G2 try this Array* formula
    [COLOR="#0000FF"]
    =IFERROR(INDEX($A$2:$A$8,SMALL(IF((ISNUMBER(MATCH($B$2:$B$8,$F$2:$F$3,0)))*($C$2:$C$8>10)*($E$2:$E$8>30),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(G$2:G2))),"")[/COLOR]


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Formula/VLOOKUP for multiple 'greater than' criteria to return multiple results


    My apologies. Yes, my references above are transposed.


    Thank you for your response! That array formula works perfectly for my need :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!