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

cm882653 · Mar 23rd 2017

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.

**NBVC** · Mar 23rd 2017

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]

cm882653 · Mar 24th 2017

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

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

Participate now!

Share