Forum Discussion

sarahmcmorris's avatar
sarahmcmorris
Copper Contributor
Dec 09, 2025
Solved

IFS Formula Help

Hello, I have been struggling with a formula and I'm hoping for some insight. I am using it to separate employees and their information to another worksheet based on which school they work for. Thei...
excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Dec 11, 2025

    XLOOKUP() function lookup_array and return_array must be same length. You mentioned only one cell E2 as return_array argument. So, amend it. Try-

    =IFS(K2="SCHOOL NAME",XLOOKUP(B2,B:B,E:E,""),AL2="SCHOOL CODE",XLOOKUP(B2,B:B,E:E,""))

    Another point is, you are looking B2 cell content inside B column, so it will always return result from E2 cell. In that case you can just use-

    =IFERROR(IFS(K2="SCHOOL NAME",E2,AL2="SCHOOL CODE",E2),"")

    But what is your actual goal? Attach a sample file or post few sample data and desired outcome.

Harun24HR's avatar
Harun24HR
Bronze Contributor
Dec 11, 2025

XLOOKUP() function lookup_array and return_array must be same length. You mentioned only one cell E2 as return_array argument. So, amend it. Try-

=IFS(K2="SCHOOL NAME",XLOOKUP(B2,B:B,E:E,""),AL2="SCHOOL CODE",XLOOKUP(B2,B:B,E:E,""))

Another point is, you are looking B2 cell content inside B column, so it will always return result from E2 cell. In that case you can just use-

=IFERROR(IFS(K2="SCHOOL NAME",E2,AL2="SCHOOL CODE",E2),"")

But what is your actual goal? Attach a sample file or post few sample data and desired outcome.

sarahmcmorris's avatar
sarahmcmorris
Copper Contributor
Dec 11, 2025

Wrapping the IFS formula in an IFERROR formula worked. Also, not using quotation marks around the school code. It is a number, while the school name is text. 

Thank you!

Resources