Search from bottom with multiple criteria

0

I am trying to search a data table in excel and find the bottom most row that meets a specific criteria. I thought I could use this LOOKUP trick but it only returns a #DIV/0 error. This is what I tried:

=LOOKUP(2,1/AND(ABS(Data!$I$2:$I$976-Calc!$D$2)<Calc!$F$1,Calc!$A6=Data!$J$2:$J$976),Data!$G$2:$G$976)

The criteria is that the value in column Data!I needs to be within a certain range of the value in Calc!D2 and the value in column Data!J needs to be equal to the value in the value in Calc!A6

I'd like to avoid VBA if I can (which is why I'm on SU and not SO).

Fr33dan

Posted 2014-07-29T14:47:37.867

Reputation: 205

Answers

0

You can use an array formula which is entered by pressing ctrl+shift+enter

To find the row in question you would use:

> =max(if(ABS(Data!$I$2:$I$976-Calc!$D$2)<Calc!$F$1,if(Data!$J$2:$J$976=Calc!$A6,row(Data!$I$2:$I$976),0),0))

if you want to return a specific cell wrap the below formula around it with the column you are interested in, in place of A

=indirect("A" & formula here)

gtwebb

Posted 2014-07-29T14:47:37.867

Reputation: 2 852

I think you left out the second if function but I think I see what you are trying to do. Make an array that is either the row number or zero if if doesn't meet the criteria. Then find the max row number. – Fr33dan – 2014-07-29T17:40:36.280

added the second if. Your analysis of what it is actually doing is spot on (it also probably should have been in my answer). – gtwebb – 2014-07-29T18:15:44.450

Asked: 2014-07-29T14:47:37.867

Viewed: 526 times

Active: 2014-07-29T18:11:59.987