Hello All
I am having a problem with an index and match and cant figure it out. I would like to match the # in Column A with the # in Column E , and if there is a YES anywhere in column L that corresponds to the # in Column A then I would like to return a YES in column B( where formula resides). If there is a NO in column L then I would like to return a NO. For some reason, I am returning a NO ( highlighted in red) instead of a YES
EXAMPLE COLORED IN ORANGE AND RED
Thanks in advance
Last edited by Ron Purpura; 12-02-2021 at 06:35 PM.
Why are you using @ in your formula. That is only for Tables.
B2 =IFNA(IF(MATCH(A2,($E$2:$E$18)*($L$2:$L$18="YES"),0)>0,"YES",""),"NO")
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Sorry Mehmetcik, not quite following you. I have no @ in the formula
This is the formula I have in cell b2: =IFNA(IF(MATCH(A2,($E$2:$E$18)*($L$2:$L$18="YES"),0)>0,"YES",""),"NO"), which is not working
Thank you for taking the time to look at this
Administrative Note:
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you.
So sorry, thread title changed
The formula I see in your workbook us this:
=IFNA(IF(MATCH(A2,(@$E$2:$E$18)*(@$L$2:$L$18="YES"),0)>0,"YES",""),"NO")
If I remove the @ signs it works correctly.
Maybe if you enter it as an array formula before copying down, it might work.
CTRL+SHIFT+ENTER
Last edited by AliGW; 12-02-2021 at 07:29 PM.
Thank you AliGW
I have no idea why an @ sign is showing up in the formula.
My workbook is reflecting : =IFNA(IF(MATCH(A2,($E$2:$E$18)*($L$2:$L$18="YES"),0)>0,"YES",""),"NO")
Very frustrating, but I greatly appreciate your help
Sorry about the thread title
One way:
=IF(COUNTIFS($E$2:$E$18,A2,$L$2:$L$18,"YES")>0,"YES","NO")
B3 shows as No in your attachment. Deleting the @ symbols returns a No.
Maybe as Ali Says you need to use Ctrl Shift Enter in B2 and then fill down.
THANK YOU SO MUCH Ali and Mehmetcik ! Utilizing the array does solve my problem.
I am curious, is there another formula I may be able to use instead of using the array ?
Gratefully
Ron
Another way:
=IF(AGGREGATE(14,6,(A2=$E$2:$E$18)*($L$2:$L$18="YES"),1)=1,"YES","NO")
AWESOME ! thank you Phuocam !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks