Forum Discussion
Shorter way or shortcut to use IF, AND, AND drawing from 3 variables
- I'm very impressed by this formula! I am tooling around with it to expand it to all courses and when the formula is on separate worksheets etc. But you've given me a great tool to work with! Thanks.
As variant
=LET(
pos, XMATCH(G3,A:A),
names, INDEX(B:B, pos+1):INDEX(B:B, pos+12),
front, INDEX(D:D, pos+1):INDEX(D:D, pos+12),
back, INDEX(E:E, pos+1):INDEX(E:E, pos+12),
XLOOKUP(B3,names, CHOOSE( (D3="FRONT")+1, back, front) )
)Just for the record, the solution that SergeiBaklan gave you is not VBA. It is a function (LET) that is only available in the newest versions of Excel, and it's a powerful one, a way to make formulas more readable and therefore more manageable. Also for the record, building deeply nested IF functions sadly goes in the opposite direction, less manageable and far less easily maintained, more error prone as a result.
Here, to help learn LET and how it works, its value, is a web reference site describing it: https://exceljet.net/excel-functions/excel-let-function
Thanks, mathetes . It was mentioned Excel for Mac, I'm not on it, but guess LET() shall be available for subscription version.
- Yes, indeed. I've been using LET without problems on my Mac, as well as FILTER and UNIQUE and others of the new functions. I do have the subscription, and get access as a Beta user as well.
- Thanks for that clarification before I disappeared into a VBA rabbit hole! To be clear, a deeply nested IF function is what I am trying to avoid, correct? Quadruple Pawn's formula seems to work pretty well. I just need to now expand it to include all courses and apply across multiple pages of the workbook.
tomeegee Nested IF() always works fine and available on any version, except it's bit hard in support.
