function to return true/na/false when value doesn't match
Hello, hope someone could help me
I have attached an excel sheet where the before sheet is the original table and the after sheet is table with the intended function (green column)
The main thing is, the id number is being "remembered" by excel to match with an equipment no. Then if a different equipment no. uses the same id number, the function will run as false.
Thanks.
I do not see any attempt to use or adapt the formula you said was working in your previous thread. Please do as much for yourself as you can first: don't expect us to start from scratch every time you slightly change your requirements. Thanks!
Last edited by AliGW; 10-13-2018 at 06:11 AM.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
[.... deleted by me; misdirection ....]
Last edited by joeu2004; 10-13-2018 at 11:21 AM. Reason: deleted
@pizzabae.... You did a good job of demonstrating your requirements.
But I did not do a good job of following them in my previous response. (Klunk!)
Try the following formula in E2 and copy down through E5:
This assumes that you consistently use nothing (empty cell) in column C as demonstrated, not dash ("-") as you use in column B.
Please Login or Register to view this content.
Last edited by joeu2004; 10-13-2018 at 11:29 AM. Reason: simpler in red
Hello AliOriginally Posted by AliGW
Get where you're coming from. My bad from being too dependent on others. Ive tried to twist it around but I still cannot get it right. I'm pretty sure I've understood it wrongly. Here is the function that I've used:
Formula:
Please Login or Register to view this content.
1) If cell B2 contains - or blank, return NA. This is the logical test and return value if it is true.
2)lookup A2 to see the equipment plate no. This is the reference value to see where the ID no. belongs to
3) Array A to C is where I will find the values to match the ID no. to the equipment plate no
4) 1 is because I am referring back to the equipment plate no. to see if the ID nos matched
5) 0 is to attain exact match
6) = C2 is for excel to see if the ID no matched, is the same as the equipment plate no
Using this function would only return NA or false. Appreaciate if you can advise me on this. I would continue trying.
Hi joeu2004
Heheh, I saw ur prev post and tried it. Actually for column C, it would not be empty and will be filled up with a new id No. So yeah, the previously posted function did not work. Understood why.
Your shorter function worked even if column C had a value or not. Ultimately, the excel attached is just a mock sheet. The actual sheet contained 700+ rows and much more columns. Hence, Ive made minor chanegs from your formula and double click down.
Formula:Please Login or Register to view this content.
Thank you so much for your help!
Last edited by jeffreybrown; 10-13-2018 at 12:05 PM. Reason: Removed full quote!
Please try at E2 and drag down
=IF(OR(B2="-",B2=""),"NA",ISERROR(MATCH(A2&B2,INDEX(A$1:A1&C$1:C1,),)))
or and press Ctrl + Shift + Enter
=IF(ISNUMBER(B2),AND(A2&B2<>A$1:A1&C$1:C1),"NA")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks