lookup multiple criteria
awesome Biff!!
you have just save my day!!
"T. Valko" wrote:
if Col C is empty, i get a zero if col B or A is
empty too and #N/A if they have values
can i get rid of those??
That's gonna result in a monster formula!
=IF(SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(She et1!C1:C10=C1)),IF(INDEX(Sheet1!D1:D10,MATCH(1,(Sh eet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10= C1),0))="","",INDEX(Sheet1!D1:D10,MATCH(1,(Sheet1! A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),0 ))),"")
You might just want to use the original formula and then use another smaller
formula in a different cell like this:
Assume the original formula is in cell X1. Then:
=IF(ISNA(X1),"",IF(X1=0,"",X1))
--
Biff
Microsoft Excel MVP
|