Dynamic Range with Function "AND"
 

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

Dynamic Range with Function "AND"
 

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

Andy.

"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

Dynamic Range with Function "AND"
 

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

Dynamic Range with Function "AND"
 

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

Dynamic Range with Function "AND"
 

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

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