lookup 2,1/ over an array of values

[jimblimm]

i have the following formula
in the formula i am trying to find the last time a value showed


LOOKUP(2,1/('MAIN DATA'!$BT$20:$BV$4000=A11),'MAIN DATA'!$A$20:$A$4000))

max index of bt:bt=a11 bu:bu=a11 bv:bv=a11


for some reason, it is not calculating right

i think i need to use mmulti function but dont know exactly how

i guess i need

index(max('MAIN DATA'!$BT$20:$Bt$4000=A11,'MAIN DATA'!$Bu$20:$Bu$4000=A11,'MAIN DATA'!$Bv$20:$BV$4000=A11 or something

 

[pgc01]

Hi

Try:

=IF(COUNTIF($BT$20:$BV$4000,A11)=0,"Not found!",INDEX($A:$A,MAX(IF($BT$20:$BV$4000=A11,ROW($BT$20:$BV$4000)))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.