OR function referencing other sheets with Index - returning INVALID COLUMN VALUE
This is driving me nuts.
Sheet1:
{Sheet1_CC} = Course Code (Text/Number Column)
{Sheet2_MRC} = Modify Replace Code (Text/Number Column)
{Sheet1_AT} = A Task Dropdown list with restricted options (NEW, MODIFY, REMOVE)
Basically a course code can be in either column and I need to pull relating task into Sheet2
Sheet2:
[Course Code]@row = text value: XYZ or ABC; used to find the AT (NEW, MODIFY, REMOVE) in Sheet 1
Tried both Text/Number and Dropdown lists columns (even with same options or empty) currently has Dropdown lists with options matching Sheet1 - has the formula:
=IF(OR(IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), 0), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), 0)), {Sheet1_AT}, "#CHECKON")
Result = #INVALID COLUMN VALUE error
But, if I do this:
=IF(OR(IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), 0), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), 0)), "good", "#CHECKON")
Result = good
it works. "good" is no different than {Sheet1_AT} both are text. I'm just supplying the text instead of pulling the text.
Taking it one IFERROR statement at a time it also works:
=IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), "#CHECKON")
Result = NEW
Basically,
Sheet 1
CC MC AT
XYZ NEW
CDE ABC MODIFY (CDE is being replaced by ABC)
Should be supplying Sheet2 with NEW based on XYZ or MODIFY based on ABC. I've broken my formula down into pieces and have checked and rechecked the arguments down to separating out each argument. Why does a sheet reference throw the error but not with provided text, even when it's the same column type (Text/Number) and both are text.
What am I missing? Thank you in advance for your help.
Stephanie L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension | Enterprise Projects
Best Answers
-
Hi @Stephanie Reedy
In this formula you had (the one which works)
=IF(OR(IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), 0), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), 0)),
"good"
, "#CHECKON")You are returning the text "good" if the Course Code@row matches Steet2_MRC or Sheet1_CC.
When you change "good" to
{Sheet1_AT}
You aren't just changing a static value to a refence. You are changing it to a range. The formula does not know which value in
{Sheet1_AT}
it should return. You need to define the row as well as the column. Does that make sense?I think you need to switch the logic all around and do something like this:
=IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), "#CHECKON"))
So now the formula will return the value in
{Sheet1_AT}
in the row where the Course Code@row matches Sheet1_CC.
If it returns a NO MATCH error,
It will return the value in
{Sheet1_AT}
in the row where the Course Code@row matches Sheet2_MRC.
If there is no match there it will return "#CHECKON"
Let me know how you get on.
-
The INDEX COLLECT formula would look something like this:
=IFERROR(INDEX(COLLECT(
{Sheet1_AT}
, {Sheet1_CC}, [Course Code]@row,{Sheet1_AM}
, "NATIONAL"), 1), IFERROR(INDEX(COLLECT({Sheet1_AT}
, {Sheet1_MRC}, [Course Code]@row,{Sheet1_AM}
, "NATIONAL"), 1), ""))This says:
If no error, return Sheet1_AT where Sheet1_CC equals the Course Code on the current row and Sheet1_AM is NATIONAL.
If no match found (i.e. there is an error), then return Sheet1_AT where Sheet1_MCR equals the Course Code on the current row and Sheet1_AM is NATIONAL.
If no match found there (error), then return nothing.
Answers
-
Hi @Stephanie Reedy
In this formula you had (the one which works)
=IF(OR(IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), 0), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), 0)),
"good"
, "#CHECKON")You are returning the text "good" if the Course Code@row matches Steet2_MRC or Sheet1_CC.
When you change "good" to
{Sheet1_AT}
You aren't just changing a static value to a refence. You are changing it to a range. The formula does not know which value in
{Sheet1_AT}
it should return. You need to define the row as well as the column. Does that make sense?I think you need to switch the logic all around and do something like this:
=IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), "#CHECKON"))
So now the formula will return the value in
{Sheet1_AT}
in the row where the Course Code@row matches Sheet1_CC.
If it returns a NO MATCH error,
It will return the value in
{Sheet1_AT}
in the row where the Course Code@row matches Sheet2_MRC.
If there is no match there it will return "#CHECKON"
Let me know how you get on.
-
Thank you SO Much. Didn't even occur to me that I could nest an IFERROR within an IFERROR, but now it makes total sense. Thank you!
Stephanie L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension | Enterprise Projects
-
Wonderful. Happy to have helped.
-
A tag on question to this. How do I wrap an IF/IFERROR around all of that to perform only if {Sheet1_AM} = NATIONAL?
Sheet1:
{Sheet1_CC} = Course Code (Text/Number Column)
{Sheet1_MRC} = Modify Replace Code (Text/Number Column)
{Sheet1_AT} = A Task Dropdown list with restricted options (NEW, MODIFY, REMOVE)
{Sheet1_AM} = 15 different Locations; I want only NATIONAL.Basically a course code can be in either column and I need to pull relating task into Sheet2
Sheet2:
Contains the formula based on [Course Code]@row
IF:=IFERROR(INDEX({Sheet1_AM}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), IFERROR(INDEX({Sheet1_AM}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), "#CHECKON"))
Returns {Sheet1_AM} = NATIONAL
THEN RUN
=IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet1_CC}, 0)), IFERROR(INDEX({Sheet1_AT}, MATCH([Course Code]@row, {Sheet2_MRC}, 0)), "#CHECKON"))
Returns {Sheet1_AT}
Otherwise return "" (if no LOCATION <> National)
So I would only get the specified Task from Sheet 1 if that record has a location of NATIONAL. Again everything is based on Sheet2's Course Code being in either {Sheet1_CC} or {Sheet1_MRC}.
Or is there another way to do this?Stephanie L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension | Enterprise Projects
-
An IF is not going to work in that scenario as it can only evaluate one row, it can't scan a column looking for a row that matches. INDEX can do the scanning. However, MATCH evaluates just one criteria to find the correct row to index. COLLECT, on the other hand will evaluate multiple criteria.
So, you need to COLLECT the rows when the course code and AM are suitable and INDEX the AT in CC. If there is no match (IFERROR) then do the second INDEX on MRC (also a COLLECT) and if no match there then return nothing (rather than returning #CHECKON).
Does that make sense?
-
The INDEX COLLECT formula would look something like this:
=IFERROR(INDEX(COLLECT(
{Sheet1_AT}
, {Sheet1_CC}, [Course Code]@row,{Sheet1_AM}
, "NATIONAL"), 1), IFERROR(INDEX(COLLECT({Sheet1_AT}
, {Sheet1_MRC}, [Course Code]@row,{Sheet1_AM}
, "NATIONAL"), 1), ""))This says:
If no error, return Sheet1_AT where Sheet1_CC equals the Course Code on the current row and Sheet1_AM is NATIONAL.
If no match found (i.e. there is an error), then return Sheet1_AT where Sheet1_MCR equals the Course Code on the current row and Sheet1_AM is NATIONAL.
If no match found there (error), then return nothing.
-
Thank you again! I had tried Collect, but kept getting turned around.
Stephanie L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension | Enterprise Projects
-
You got there in the end. 😀
If you need to evaluate a range just forget about IF and think about INDEX. Otherwise, you will be chasing your tail.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!