Hello,
I am having some problems using XLOOKUP as my lookup value is generated by a formula, I am therefore getting the dreaded #NA. The formula works and the value is definitely in the array as if I manually type the value, it can find it.
I am trying to use the value in F3 which has the below formula in:
=IF(ISBLANK(B3),"",MID(B3,FIND("Member:",B3)+7,FIND("Automated",B3)-FIND("Member:",B3)-7))
The XLOOKUP Formula is:
=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(XLOOKUP(F3, Sheet1!C:C, Sheet1!A:A), XLOOKUP(F3, Sheet1!D:D, Sheet1!A:A)), XLOOKUP(F3, Sheet1!E:E, Sheet1!A:A)), XLOOKUP(F3, Sheet1!F:F, Sheet1!A:A)), XLOOKUP(F3, Sheet1!G:G, Sheet1!A:A)), XLOOKUP(F3, Sheet1!H:H, Sheet1!A:A)), XLOOKUP(F3, Sheet1!I:I, Sheet1!A:A)), XLOOKUP(F3, Sheet1!J:J, Sheet1!A:A)), XLOOKUP(F3, Sheet1!K:K, Sheet1!A:A))
As a note, F3 produces a code such as "R4G6" and the lookup produces a name.
Any help would be much appreciated.
P.s any tips on getting the long formulas shortened would also be welcomed if possible!
I am having some problems using XLOOKUP as my lookup value is generated by a formula, I am therefore getting the dreaded #NA. The formula works and the value is definitely in the array as if I manually type the value, it can find it.
I am trying to use the value in F3 which has the below formula in:
=IF(ISBLANK(B3),"",MID(B3,FIND("Member:",B3)+7,FIND("Automated",B3)-FIND("Member:",B3)-7))
The XLOOKUP Formula is:
=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(XLOOKUP(F3, Sheet1!C:C, Sheet1!A:A), XLOOKUP(F3, Sheet1!D:D, Sheet1!A:A)), XLOOKUP(F3, Sheet1!E:E, Sheet1!A:A)), XLOOKUP(F3, Sheet1!F:F, Sheet1!A:A)), XLOOKUP(F3, Sheet1!G:G, Sheet1!A:A)), XLOOKUP(F3, Sheet1!H:H, Sheet1!A:A)), XLOOKUP(F3, Sheet1!I:I, Sheet1!A:A)), XLOOKUP(F3, Sheet1!J:J, Sheet1!A:A)), XLOOKUP(F3, Sheet1!K:K, Sheet1!A:A))
As a note, F3 produces a code such as "R4G6" and the lookup produces a name.
Any help would be much appreciated.
P.s any tips on getting the long formulas shortened would also be welcomed if possible!