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

  • Thread starter Thread starter se7098
  • Start date Start date
S

se7098

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?
 
=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.
 
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?
 
=SUMPRODUCT(--(B2:B100="Miami")*NOT(ISERR(SEARCH("bilingual",A2:A100)))*(C2:C100))

Hope this helps.
 
It worked! OMG...you have NO idea how much time this is going to save
me...thank you, thank you, thank you!
 
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?
 
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.
 
you are a genius...saved my life...again!

John C said:
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.
Click to expand...
 
Back
Top