OR function referencing other sheets with Index - returning INVALID COLUMN VALUE

Stephanie Reedy

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.

KPH

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.

KPH

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.

KPH

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.

Stephanie Reedy

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!

KPH

Wonderful. Happy to have helped.

Stephanie Reedy

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?

KPH

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?

KPH

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.

Stephanie Reedy

Thank you again! I had tried Collect, but kept getting turned around.

KPH

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.