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
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,
- Red is the range for {Shift Roster Range 5}
- Blue is the range for {Shift Roster Range 8}
- 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!
Answers
-
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.
-
Got it fixed!. For index match you need to match the Y axis first then the X axis. This fixed the formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!