Vlookup Filtered Rows

[VegasL]

Hello,

I am trying to compare tab 1 "Food", to tab 2 "Desired Result".

In the Food Tab, I have placed a custom filtered in column B to show greater than 2, thus it's filtering out 2 rows, as it should.

The problem is, in the 2nd tab, "Desired Result", I want to compare the 2 sheets/columns, and only show the filtered results, but it isn't working. It is also listing tomatoes, and 1 day in freezer, which it shouldn't

it should list beans and salad, and their corresponding days in freezer, else "
.

The formula I used is =IF(ISNA(VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)),"",VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE))

but it isn't correct.

Any help is appreciated.

[martindwilson]

you could use on desired result sheet
=SUMPRODUCT(--(Food!$A$2:$A$20='Desired Result'!A4),--(Food!$B$2:$B$20>2),(Food!$B$2:$B$20))
or
=IF(ISNA(VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)),"",IF(VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)<=2,"",VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)))

[VegasL]

Thank you Martin. Few questions:

On =SUMPRODUCT(--(Food!$A$2:$A$20='Desired Result'!A4),--(Food!$B$2:$B$20>2),(Food!$B$2:$B$20)) shoudn't the range be 5, instead of 20 as below?

=SUMPRODUCT(--(Food!$A$2:$A$5='Desired Result'!A4),--(Food!$B$2:$B$5>2),(Food!$B$2:$B$5))

Also, while this did solve the problem, it is putting 0's for all values that should be blank, any way to make it blank for the o's?


Please, if you can translate this into easier to follow logic:

=IF(ISNA(VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)),"",IF(VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)<=2,"",VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)))

I'm interpreting us, if the

1st do a vlookup i.e. (VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)), if its true that this will result in N/A hence (ISNA, then put "", ELSE (IF It's true that its NOT N/A, then do IF(VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)<=2, where you're only showing values less than or equal to 2, otherise show blank, else do VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)))

Basically, it's saying do first vlookup, if it's true that its not equal to N/A, then place "". then your'e saying also do for all values not N/A and Less than equal to 2, do "", otherwise do VLOOKUP('Desired Result'!A3,Food!$A$3:$B$5,2,FALSE)))

is this correct?


Also, can you explain in laymans term the sum product like above ?

Thanks

[martindwilson]

1.yes
2. no lol
sum product needs the range to be = , a1:a20 can be compared against b1:b20 or b21:b40 i just allowed a margin of error
if you dont want 0
then
if(SUMPRODUCT(--(Food!$A$2:$A$5='Desired Result'!A4),--(Food!$B$2:$B$5>2),(Food!$B$2:$B$5))=0,"",SUMPRODUCT(--(Food!$A$2:$A$5='Desired Result'!A4),--(Food!$B$2:$B$5>2),(Food!$B$2:$B$5)))