| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a match and display a message
I am just delving into Excel other than the basics and I am trying to find a
way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
=if(a1="mmm....doughnuts","found","not found")
******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
Ok that works thanks.. Is it possiable to have multiple IF's on the same line?
Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
maybe this is what you're looking for
=IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
Oh nice thanks.. I would need to do this for 80 variables. Is there a better
way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
how about posting an example of what you have and what you would like?
-- Gary "Woody" wrote in message ... Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
If what I am looking for is in found in Row 1 then return a message
"Something" if it is in Row 2 return some other message. Sheet 1 A1 Enter Letter: A2 (function to search sheet 2 and return a message to B2) B1 Rate: B2 "message" Sheet 2 A1 B1 C1 D1 Row 1 A B C D Row 2 E F G H "Gary Keramidas" wrote: how about posting an example of what you have and what you would like? -- Gary "Woody" wrote in message ... Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
If you have 80 things to look through, I'd insert a new worksheet and put those
80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
This seems to only work if my data I am looking for is in A1 on sheet 2. Does
not seem to search A1 to A80. I tried also changing the values in the formaula to say A1:A$ and that did not work either. I am so close.... Thank you all for the help. =if(isnumber(match(a1,sheet2!a1:a$,0)),"Found","no t found") "Dave Peterson" wrote: If you have 80 things to look through, I'd insert a new worksheet and put those 80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
Never Mind.. I just got it... I just realized that A:A is searching the
column not the row.. So when I changed it as a test to A1:F1 that worked. Thanks all again "Dave Peterson" wrote: If you have 80 things to look through, I'd insert a new worksheet and put those 80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
Once your list hits 257 elements, you'll be happier to have used columns <vbg.
Woody wrote: Never Mind.. I just got it... I just realized that A:A is searching the column not the row.. So when I changed it as a test to A1:F1 that worked. Thanks all again "Dave Peterson" wrote: If you have 80 things to look through, I'd insert a new worksheet and put those 80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
Okay I got it all working with the following looking at 4 rows and returning
the results to 4 different cells. I was trying to make it as one function now so I can have the result returned to 1 cell. I would use the OR somehow but can not seem to get the syntax right. =IF(ISNUMBER(MATCH(B2,Suggest!B1:Q1,0)),"Add","") =IF(ISNUMBER(MATCH(B2,Suggest!B2:I2,0)),"Add","") =IF(ISNUMBER(MATCH(B2,Suggest!B3:Q3,0)),"Delete or Remove","") =IF(ISNUMBER(MATCH(B2,Suggest!B4:AO4,0)),"Delete or Remove","") Thanks for all the great help.. "Dave Peterson" wrote: Once your list hits 257 elements, you'll be happier to have used columns <vbg. Woody wrote: Never Mind.. I just got it... I just realized that A:A is searching the column not the row.. So when I changed it as a test to A1:F1 that worked. Thanks all again "Dave Peterson" wrote: If you have 80 things to look through, I'd insert a new worksheet and put those 80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
When you look at the results of these 4 formulas, do you see at most one non ""
cell? If yes, you could use a 5th formula: =x1&x2&x3&x4 (x1, x2, ... are the cells with those formulas) or even: =IF(ISNUMBER(MATCH(B2,Suggest!B1:Q1,0)),"Add","") &IF(ISNUMBER(MATCH(B2,Suggest!B2:I2,0)),"Add"," ") &IF(ISNUMBER(MATCH(B2,Suggest!B3:Q3,0)),"Delete or Remove","") &IF(ISNUMBER(MATCH(B2,Suggest!B4:AO4,0)),"Delet e or Remove","") All one cell. (concatenating "" with other text won't hurt.) Woody wrote: Okay I got it all working with the following looking at 4 rows and returning the results to 4 different cells. I was trying to make it as one function now so I can have the result returned to 1 cell. I would use the OR somehow but can not seem to get the syntax right. =IF(ISNUMBER(MATCH(B2,Suggest!B1:Q1,0)),"Add","") =IF(ISNUMBER(MATCH(B2,Suggest!B2:I2,0)),"Add","") =IF(ISNUMBER(MATCH(B2,Suggest!B3:Q3,0)),"Delete or Remove","") =IF(ISNUMBER(MATCH(B2,Suggest!B4:AO4,0)),"Delete or Remove","") Thanks for all the great help.. "Dave Peterson" wrote: Once your list hits 257 elements, you'll be happier to have used columns <vbg. Woody wrote: Never Mind.. I just got it... I just realized that A:A is searching the column not the row.. So when I changed it as a test to A1:F1 that worked. Thanks all again "Dave Peterson" wrote: If you have 80 things to look through, I'd insert a new worksheet and put those 80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find a match and display a message
That works perfect! Thank you so much
"Dave Peterson" wrote: When you look at the results of these 4 formulas, do you see at most one non "" cell? If yes, you could use a 5th formula: =x1&x2&x3&x4 (x1, x2, ... are the cells with those formulas) or even: =IF(ISNUMBER(MATCH(B2,Suggest!B1:Q1,0)),"Add","") &IF(ISNUMBER(MATCH(B2,Suggest!B2:I2,0)),"Add"," ") &IF(ISNUMBER(MATCH(B2,Suggest!B3:Q3,0)),"Delete or Remove","") &IF(ISNUMBER(MATCH(B2,Suggest!B4:AO4,0)),"Delet e or Remove","") All one cell. (concatenating "" with other text won't hurt.) Woody wrote: Okay I got it all working with the following looking at 4 rows and returning the results to 4 different cells. I was trying to make it as one function now so I can have the result returned to 1 cell. I would use the OR somehow but can not seem to get the syntax right. =IF(ISNUMBER(MATCH(B2,Suggest!B1:Q1,0)),"Add","") =IF(ISNUMBER(MATCH(B2,Suggest!B2:I2,0)),"Add","") =IF(ISNUMBER(MATCH(B2,Suggest!B3:Q3,0)),"Delete or Remove","") =IF(ISNUMBER(MATCH(B2,Suggest!B4:AO4,0)),"Delete or Remove","") Thanks for all the great help.. "Dave Peterson" wrote: Once your list hits 257 elements, you'll be happier to have used columns <vbg. Woody wrote: Never Mind.. I just got it... I just realized that A:A is searching the column not the row.. So when I changed it as a test to A1:F1 that worked. Thanks all again "Dave Peterson" wrote: If you have 80 things to look through, I'd insert a new worksheet and put those 80 terms in A1:A80. Then I could use: =if(isnumber(match(a1,sheet2!a:a,0)),"Found","not found") Woody wrote: Oh nice thanks.. I would need to do this for 80 variables. Is there a better way to have it look at a whole Row? "Gary Keramidas" wrote: maybe this is what you're looking for =IF(OR(A1="a",A1="b",A1="c",A1="d"),"found","not found") -- Gary "Woody" wrote in message ... Ok that works thanks.. Is it possiable to have multiple IF's on the same line? Where if the value I put in is A or B or C or D it returns the value? example: =if(a1="A;B;C;D","found","not found") "Anne Troy" wrote: =if(a1="mmm....doughnuts","found","not found") ******************* ~Anne Troy www.OfficeArticles.com "Woody" wrote in message ... I am just delving into Excel other than the basics and I am trying to find a way to search a row and if the variable is found I want to return to another cell a message like "Value Found" or something like that. Not sure which Function to use.. Any ideas? Thank you -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| Cross match data in Col A v/s Col B and display match in Col 3 | Excel Discussion (Misc queries) | |||
| Display a message | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| How to display a message box | Excel Programming | |||
Linear Mode
