Help With Index Match (#Invalid value)

JPFORDTE

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

This is what I want it to look like. ^

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

This is the Formula I have used. ^

^ 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}

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!

Paul Newcome

I would start with specifying an exact match in both of your MATCH functions.

=MATCH("text", {Range}, 0)

If that still doesn't clear it up, double check your columns are in fact contact type columns and you ranges are in fact correct. The first range should reference the entire table. The second range should reference only the [Week + Day*] column, and the third range should reference only the first row.

Also make sure that particular error is not present anywhere in the reference table.

JPFORDTE

Got it fixed!. For index match you need to match the Y axis first then the X axis. This fixed the formula.