Dynamic Range with Function "AND"

J

Jeff

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?
 
G

Guest

Hi
You could use VLOOKUP or INDEX and MATCH but it's difficult to advise
without a bit more information.

Andy.
 
B

Bob Phillips

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)
 
J

Jeff

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 said:
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)
Click to expand...
 
B

Bob Phillips

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="Mescada"),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 said:
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"?
Click to expand...
 
J

Jeff

Thank you
--
Regards,
Jeff



Bob Phillips said:
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="Mescada"),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)
Click to expand...
 
Top