title: Possible to rewrite or condense an IFS formula with multiple conditions to run faster
body: <p>I'm pretty new to formulas and just joined the forum, but I'm completely stumped on a pet project I've been doing for work using Google Sheets. I'm trying to create this sheet to be a feeder page for a product upload template that I designed. I'm pretty sure what I'm trying to do can be done, but I think I've taken this as far as I can by following examples from the internet. Basically, there's a list of skus filled into column F and several drop menu's in column C with options of how to sort that list. Every option in the drop menu corresponds with a named range, and all of the named ranges are sorted and displayed in columns AP-BJ.</p>
  <p>The 4 'initial parent sku' drop menu's (C4-C7) are supposed to filter the full list of skus and return only those that match with the chosen named-range option into the corresponding column U, Z, AE or AJ - preferably as an array and removing blank lines. It is currently set up to detect a string within the sku instead of basing the search on a named range. For testing purposes, the lists displayed in columns U, Z and AE are correct using this formula in U3: [=IF(C4=&quot;&quot;,&quot;&quot;,INDEX(FILTER(F$3:F,SEARCH(C4,F$3:F))))]</p>
  <p>The 12 'secondary parent sku' drop menu's (C9-C24) are then meant to reference the lists created by the initial parent filter (U, Z, AE or AJ) and sort those lists one step further. I'm sure it's a clunky mess since I'm learning as I create this, but I finally got it to sort-of work by using the following IFS statement with multiple conditions [=iferror(ifs(C$9=&quot;High&quot;,INDEX((High),(MATCH(U3:U,AZ$3:AZ,0))),C$9=&quot;Lux&quot;,INDEX((Lux),(MATCH(U3:U,BB$3:BB,0))),C$9=&quot;DIA&quot;,INDEX((DIA),(MATCH(U3:U,AX$3:AX,0))),C$9=&quot;Gem&quot;,INDEX((Gem),(MATCH(U3:U,BD$3:BD,0)))))].</p>
  <p>The biggest problem is speed. I haven't even included all of the named ranges that are available in the drop menu's or copied the formula into all 12 columns it would need to be in, and the sheet is hanging while trying to work through the formulas. Speed issue aside, it also doesn't seem to work if I add an array formula to the beginning and some skus are 1000's of rows. It would be nice to remove the blank lines but I can't seem to work that into the formula without breaking it, either.</p>
  <p>Any tips, help, or advice on material to reference would be so appreciated.
  <a href="https://i.stack.imgur.com/0lYCd.jpg" rel="nofollow noreferrer">Screenshot of sheet</a></p>
  <p>Link to sample of sheet:
  <a href="https://docs.google.com/spreadsheets/d/1fnHwaaPOKyKv9rek_Hhajb_8_vU4ssVV/edit?usp=sharing&amp;ouid=109394567075019147581&amp;rtpof=true&amp;sd=true" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1fnHwaaPOKyKv9rek_Hhajb_8_vU4ssVV/edit?usp=sharing&amp;ouid=109394567075019147581&amp;rtpof=true&amp;sd=true</a></p>