Hi All,
I need your help to advice how to combine two iferror formula into one because I have formula from 2 table which I want to find the missing in either table which shows result in one cell.
=IFERROR(IF(AND(MATCH(Sheet2!E3,Sheet3!D3:O3,0),MATCH(Sheet2!G3,Sheet3!D3:O3,0)),"A","B"),"C")
=IFERROR(IF(AND(MATCH(Sheet3!E3,Sheet2!D3:O3,0),MATCH(Sheet3!G3,Sheet2!D3:O3,0)),"A","B"),"F")
Thanks and appreciate your help and advice.
Regards,
Kenji
Last edited by AliGW; 07-03-2020 at 02:51 AM. Reason: Urgency statement removed from thread title.
Welcome to the forum.
In what order do you want the outcomes to be applied? Strictly speaking it can't be done, but if you can explain, IN WORDS, the way that you want Excel to process this, then we can help you to build a combined formula that will work.
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.
Hi Ali,
Thanks for your replied.
I have 2 tables in 2 tabs.
First formula I want to match the data in first table to second table to see whether the data in first table exist in second table or not. If it is yes then answer as "A", if cannot match mean the data in first table doesn't exist in second table result in "C".
Second formula I do is the same as first formula just vice versa which I want to check the data in second table exist in first table or not. If it is yes then answer will be same as "A", if cannot match means missing in first table for data in second table then result in "F".
Not sure whether I have express clearly or not. Let me know in case need further clarification.
So where does the "B" come in? Can that be removed?
The problem is that if you try to nest IFERROR statements and the first one is triggered, you will never get to evaluating the second. Calculations stop as soon as Excel finds something that's true.
Here's what Excel 'thinks':
1. Check the first table.
2. Find a match - return "A" OR can't find a match - return "C".
3. STOP
It won't go any further because either way it has found a satisfactory result.
Last edited by AliGW; 07-03-2020 at 03:15 AM.
Hi Ali
Yes. B can be remove because it is part of the IF formula that I thought the result with error which will become FALSE then will result as "B" but I don't see "B" is working then I only put in IFERROR.
Sorry that I'm poor in excel that is why doesn't create good formula.
OK - please read what I added to my previous post.
If you tell Excel what to return if it doesn't find a match in the first lookup, it will stop there and never move on to the second lookup, so you need to decide how you want it to deal with this.
You can do this:
=IFERROR(IF(AND(MATCH(Sheet2!E3,Sheet3!D3:O3,0),MATCH(Sheet2!G3,Sheet3!D3:O3,0)),"A",IF(AND(MATCH(Sheet3!E3,Sheet2!D3:O3,0),MATCH(Sheet3!G3,Sheet2!D3:O3,0)),"A","F")),"C")
Hi Ali,
Thanks again for your feedback. In that case, I believe the previous formula won't show up "F" since those error will just end in "C".
Can I ask that if the end result ending as "C" then we build argument for "C" to check whether "C" is meant for data missing in first table (first table check with second table) or second table (second table check with first table)?
Sorry - this is getting too complicated to contemplate (for me) without seeing it in context.
There are instructions at the top of the page explaining how to attach your sample workbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks