lookup multiple search criteria but only return a value if both tr
 

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if both tr
 

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2: A100)))*(C2:C100))

Hope this helps.
--
John C


"se7098" wrote:

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

It worked! OMG...you have NO idea how much time this is going to save
me...thank you, thank you, thank you!

"John C" wrote:

=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2: A100)))*(C2:C100))

Hope this helps.
--
John C


"se7098" wrote:

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

Thanks for the feedback, and don't forget to check the little box below :)
--
John C


"se7098" wrote:

It worked! OMG...you have NO idea how much time this is going to save
me...thank you, thank you, thank you!

"John C" wrote:

=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2: A100)))*(C2:C100))

Hope this helps.
--
John C


"se7098" wrote:

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

definitely :)

new issue...now i need to be able to bring back the value of all those that
do NOT contain bilingual in column a...same exact set up...is that doable?


"John C" wrote:

Thanks for the feedback, and don't forget to check the little box below :)
--
John C


"se7098" wrote:

It worked! OMG...you have NO idea how much time this is going to save
me...thank you, thank you, thank you!

"John C" wrote:

=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2: A100)))*(C2:C100))

Hope this helps.
--
John C


"se7098" wrote:

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

Assuming you are still wanting the Miami match, but the total that don't have
bilingual somewhere, just remove the NOT from you statement given earlier.
--
John C


"se7098" wrote:

definitely :)

new issue...now i need to be able to bring back the value of all those that
do NOT contain bilingual in column a...same exact set up...is that doable?


"John C" wrote:

Thanks for the feedback, and don't forget to check the little box below :)
--
John C


"se7098" wrote:

It worked! OMG...you have NO idea how much time this is going to save
me...thank you, thank you, thank you!

"John C" wrote:

=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2: A100)))*(C2:C100))

Hope this helps.
--
John C


"se7098" wrote:

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?

lookup multiple search criteria but only return a value if bot
 

you are a genius...saved my life...again!

"John C" wrote:

Assuming you are still wanting the Miami match, but the total that don't have
bilingual somewhere, just remove the NOT from you statement given earlier.
--
John C


"se7098" wrote:

definitely :)

new issue...now i need to be able to bring back the value of all those that
do NOT contain bilingual in column a...same exact set up...is that doable?


"John C" wrote:

Thanks for the feedback, and don't forget to check the little box below :)
--
John C


"se7098" wrote:

It worked! OMG...you have NO idea how much time this is going to save
me...thank you, thank you, thank you!

"John C" wrote:

=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2: A100)))*(C2:C100))

Hope this helps.
--
John C


"se7098" wrote:

it does help for one of my situations...thank you very much.

however, i also have a situation where bilingual is in a string of words...

i.e., Home Solutions Specialists Bilingual

is there a way for it to just find the word bilingual within a string of
words in column a & then find miami in column b & return the value from
column c? or am i wishing for the stars?

"John C" wrote:

=SUMPRODUCT(--(A2:A100="bilingual"),--(B2:B100="miami"),(C2:C100))

Hope this helps.
Note: If there are multiple occurances of bilingual miami, then the values
in C will be added together.
--
John C


"se7098" wrote:

is there a way to lookup multiple criteria but only return the value if both
criteria true?

i.e., column a find "bilingual" column b find "miami" return value found in
column c?