Dynamic Range with Function "AND"
Thank you
--
Regards,
Jeff
"Bob Phillips" wrote:
In that case I think you need
=IF(ISNA(MATCH(1,(A1:A100=1)*(B1:B100=6)*(E1:E100= "Mescada"),0)),0,INDEX(F1:
F100,MATCH(1,(A1:A100=1)*(B1:B100=6)*(E1:E100="Mes cada"),0)))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
--
HTH
Bob Phillips
(replace xxxx in email address with googlemail if mailing direct)
"Jeff" wrote in message
...
Thank you Bob.
=IF(AND(COUNTIF(A:A,1),COUNTIF(B:B,6),COUNTIF(E:E, "Mescada")),F2,0)
One more thing: How can I make "F2" dynamic since the function must
evaluate
columns A, B, E and must take the value associated in column "F"?
--
Regards,
Jeff
"Bob Phillips" wrote:
Is this any good Jeff?
=IF(AND(COUNTIF(A:A,1),COUNTIF(B:B,6),COUNTIF(E:E, "Mescada")),F2,0)
--
HTH
Bob Phillips
(replace xxxx in email address with googlemail if mailing direct)
"Jeff" wrote in message
...
Hi,
I urgently need help with this function:
=IF(AND(A2=1,B2=6,E2="Mescada"),F2,0)
The problem that I have is that sometime the value that I look up for
is
not
located on row 2. It could be on row 5 or 11 depending on the size of
my
file.
Is there anyway I could make a generic function that would allow me to
look
up my value in range instead of a row?
--
Regards,
Jeff
|