Help With Index Match (#Invalid value)

I am trying to return a Contact value Cell from a lookup table, in the Column "Assigned Contact Email" according to "Week * + Day*" and "Shift Assigned To" in that Row

image.png

This is what I want it to look like. ^

image.png

This is the other sheet with the look up table. ^

image.png

This is the Formula I have used. ^

image.png

^ For the formula,

  1. Red is the range for {Shift Roster Range 5}
  2. Blue is the range for {Shift Roster Range 8}
  3. Pink is the rang for {Shift Roster Range 9}


image.png

Using my formula it returns #Invalid Value. Both the return column and the lookup table are all Contact type columns, so not sure where the problem lies.


Many thanks in advance!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!