This seems like a simple task but I'm not sure why my nest IF formula is not working.
Basically I have different columns with different classifications.
I want a formula that:
Check if cell A2 is empty, if yes retrun B2, IF B2 is empty return C2, if C2 is empty return D2.
Use this array formula:
=IFERROR(INDEX(A2:D2,MATCH(FALSE,ISBLANK(A2:D2),0)),"")
Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
Your profile says that you are using Excel 2003. But you have attached an xlsx file (2007 and later).
If you need Excel 2003 compatability, use this array formula instead:
=IF(ISERROR(INDEX(A2:D2,MATCH(FALSE,ISBLANK(A2:D2),0))),"",INDEX(A2:D2,MATCH(FALSE,ISBLANK(A2:D2),0)))
if you are using only 2007, please amend your profile.
=IF(ISERROR(INDEX(A2:D2,MATCH(FALSE,ISBLANK(A2:D2),0))),"",INDEX(A2:D2,MATCH(FALSE,ISBLANK(A2:D2),0)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
With your file...
Non-array (just ENTER) alternative:
=IFERROR(INDEX(A2:D2,MATCH(TRUE,INDEX((A2:D2<>0),0),0)),"")
Forum duplicate. Ignore this post of mine.
Last edited by Glenn Kennedy; 10-03-2016 at 03:40 AM.
Or try this ...
=IFERROR(INDEX(B2:D2,MATCH(1,(A2:C2="")*(B2:D2<>""),0)),"")
Enter with Ctrl+Shift+Enter
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks