Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
This is a simple formula, I know, and I wish the formula wizard was simple,
too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Try this, Connie:
=IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"No","Yes") then copy down as required. Hope this helps. Pete On Nov 10, 4:34*pm, Connie Martin wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! *Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. *Simple enough, I think. * Thank you. Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
=isnumber(match(a1,sheet1!c:c,0))
will return True or false =if(isnumber(match(a1,sheet1!c:c,0)),"Yes","no") Connie Martin wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Maybe
=IF(COUNTIF(Sheet1!$A$1:$A$50,A1)1,"Yes","No") Mike "Connie Martin" wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
This works, too! Thank you so much. Connie
"Dave Peterson" wrote: =isnumber(match(a1,sheet1!c:c,0)) will return True or false =if(isnumber(match(a1,sheet1!c:c,0)),"Yes","no") Connie Martin wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Thank you! This works wonderful. Found two numbers that weren't in my list!
Great! That makes life easier!! Connie "Pete_UK" wrote: Try this, Connie: =IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"No","Yes") then copy down as required. Hope this helps. Pete On Nov 10, 4:34 pm, Connie Martin wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Sorry, Mike, but this one gives me "No" all the way down the column which
isn't correct. Thank you for responding. I recognize your name. You've been a help many times, and I appreciate very much you responding. Connie "Mike H" wrote: Maybe =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)1,"Yes","No") Mike "Connie Martin" wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Mike's formula is looking for more than one occurrence.
You could try: =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)0,"Yes","No") or =IF(COUNTIF(Sheet1!$A:$A,A1)0,"Yes","No") Connie Martin wrote: Sorry, Mike, but this one gives me "No" all the way down the column which isn't correct. Thank you for responding. I recognize your name. You've been a help many times, and I appreciate very much you responding. Connie "Mike H" wrote: Maybe =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)1,"Yes","No") Mike "Connie Martin" wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
You're welcome, Connie - thanks for feeding back.
Pete On Nov 10, 4:55*pm, Connie Martin wrote: Thank you! *This works wonderful. *Found two numbers that weren't in my list! *Great! *That makes life easier!! *Connie "Pete_UK" wrote: Try this, Connie: =IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"No","Yes") then copy down as required. Hope this helps. Pete On Nov 10, 4:34 pm, Connie Martin wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! *Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. *Simple enough, I think. * Thank you. Connie- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Thanks, Dave. These both work, as well. Amazing how many ways it can be
done! Thank you. Connie "Dave Peterson" wrote: Mike's formula is looking for more than one occurrence. You could try: =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)0,"Yes","No") or =IF(COUNTIF(Sheet1!$A:$A,A1)0,"Yes","No") Connie Martin wrote: Sorry, Mike, but this one gives me "No" all the way down the column which isn't correct. Thank you for responding. I recognize your name. You've been a help many times, and I appreciate very much you responding. Connie "Mike H" wrote: Maybe =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)1,"Yes","No") Mike "Connie Martin" wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX? MATCH?
Hi,
Let's go back to Mike formula and give him credit, he just wrote it wrong: Instead of =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)1,"Yes","No") It should have been =IF(COUNTIF(Sheet1!$A$1:$A$50,A1)=1,"Yes","No") but this could be shortened as follows: =IF(COUNTIF(Sheet1!C:C,A1),"Yes","No") or if an answer of 0 or FALSE is ok, where false means it wasn't found and 0 means it was: =IF(COUNTIF(Sheet1!C:C,A1),) or if you use the version below if 0 appears it was not found, otherwise it was: =COUNTIF(Sheet1!C:C,A1) Cheers, Shane -- "Connie Martin" wrote: This is a simple formula, I know, and I wish the formula wizard was simple, too!! Can't get it, so I need someone's help. In cell C1 in Sheet2 I need a formula that will look at the numbers in Col. A in the same worksheet and see if the number is in Col. C of Sheet1, and return "Yes" if it is, and "No" if it isn't. Simple enough, I think. Thank you. Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |