| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for multiple criteria, and return yet another | Excel Worksheet Functions | |||
| V lookup with 2 criteria to return results for multiple columns | Excel Worksheet Functions | |||
| Multiple search criteria to return one response | Excel Worksheet Functions | |||
| Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
| How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Linear Mode
