Forum Discussion
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. Their school assignment is notated by either their school name column (K), school code column (AL), or both.
This is the formula:
=IFS(K2="SCHOOL NAME",XLOOKUP(B2,B:B,E2,""),AL2="SCHOOL CODE",XLOOKUP(B2,B:B,E2,""))
I am trying to use an IFS statement to pull data from one sheet to another if the value meets at least 1 of 2 conditions. The formula seems to stop at the first value, even if the second value is true. That is, if an employee doesn't have a certain school name in column K but has the corresponding school code in column AL, their information is not pulling to the new worksheet.
How I would like the formula to work:
-Jane Doe has "X Elementary" in column K, then her information from the XLOOKUP will populate into the cell. (The spreadsheet has a lot of information, and I only need a few columns to pull to the new worksheet.)
-If not, the formula will continue to search in column AL for the school code.
-If the specific school code is found, then her information from the XLOOKUP will populate into the cell. If nothing matches the criteria, then a blank value will be returned.
Thank you in advance!
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.
2 Replies
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.
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!
