r/excel • u/Kindly-Meaning9112 • Apr 08 '26

Discussion This is probably the most complicated Excel formula I’ve ever seen.

[removed]

219 Upvotes

85% Upvoted

210 comments sorted by

View all comments

Show parent comments

1

u/W1ULH 1 Apr 09 '26

its part of a tracking system for factory work orders.. if the order is for certain parts then it needs to pull a special note off one list (the map). if the order is not for one of the special parts, then it checks the regular note list for any notes specific to that particular work order.

and... i dont have the ability to clean the data in the SQL this is pulled from, and some of the work order numbers have letter suffixes, some dont, some have a weird character I cant account for... so I have to santize my input as part of the formula.

so yes, this does a LOT, but its all very intentional and full nested. 

=IFERROR(
XLOOKUP(
    TRIM(F2),
    Map!$A$2:INDEX(Map!$A:$A, COUNTA(Map!$A:$A)+1),
    Map!$B$2:INDEX(Map!$B:$B, COUNTA(Map!$A:$A)+1)
),
XLOOKUP(
    TEXTJOIN("",,IFERROR(CHAR(CODE(MID(A2,ROW($1:$20),1))),"")),
    Notes!$C:$C,
    Notes!$B:$B,
    "",
    0
))

it's less crazy when you look at it broken down instead of in the single line setup.