XLookup formula returns FALSE only for some rows

[NicBKK]

Hello Gurus,

I am trying to get a return value in a column depending on the name of a software a factory uses.
I did what I thought was a simple formula but in returns "False" for some rows.

Formula: =IF(O4="opera",XLOOKUP($A$1,Factory[Code],Factory[Employees],IF(O4="Symphony",XLOOKUP($A$1,Factory[Code],Factory[POS],IF(O4="Material control",XLOOKUP($A$1,Factory[Code],Factory[POS],IF(O4="sun",XLOOKUP($A$1,Factory[Code],Factory[SUN]))))))))

File attached

Thanks a lot

[6StringJazzer]

Your formula is nested incorrectly. Your nested IF functions are inside each XLOOKUP. You need to have each option at the top level, not nesting them. The best way to do this is with the IFS function. See attached file.

Here is what the updated formula looks like broken out to show the logical structure:

Please Login or Register  to view this content.

[Fluff13]

Personally I avoid using IFS as it is far less efficient than a nested IF, so it would be

Please Login or Register  to view this content.

[Bo_Ry]

Please try

=INDEX(Factory,XMATCH(A1,Factory[Code]),XMATCH(SWITCH(O4:O15,"opera","Employees","sun","SUN","POS"),Factory[#Headers]))

[NicBKK]

Thank you everyone, all worked like a charm 😊

[Fluff13]

Glad to help & thanks for the feedback.