Invalid column value when both columns are text

Katie.works

hello! So this formula worked for a second and then for some reason stopped when i changed the return text from its original 'test' to 'half' . But it's still working on the column to return dates its just the text/number rows not working. The reference and return columns are all text/numbers.

I am trying to reference a column that is prefilled with numbers to check a column on another sheet and if there's a match then return the winner name that is entered,

=IF(CONTAINS([Card Refrence #]@row , {Xchange Point Card Submission Range 1}), {Xchange Point Card Submission Range 2}, " ")

sheet with prefilled reference numbers

the sheet I am referencing and returning the name from

Paul Newcome

Double check your ranges. I see that [Reason For Win] in the target sheet is producing that error, but I notice that {Reason For Winning} in the reference sheet isn't actually being referenced. I also see that only the top row in your reference sheet has the date being referenced as opposed to the entire column.

Keep in mind though, I don't think that your formula will produce the expected results even after you troubleshoot the error. I think what you need is an INDEX/MATCH instead.

=INDEX({Column To Pull Over}, MATCH([Card Reference #]@row, {Card Reference Column}, 0))

Kerry St. Thomas

My suspicion is that it's something to do with the leading zeroes in one or the other sheet. I suggest that you delete those zeroes from both columns. If you need to keep the leading zeroes, try using helper columns with =VALUE([Card #]@row) as column formulas and using those helper columns as the basis of your formula.

Good luck!

Katie.works

Thanks for trying. It still is returning the invalid column type even when using the value helper column, there seems to be an issue with possible the sheet? I was running tests and none of the rows could be recognized as text even if i deleted everything in there and typed 'abc' in it still returns the invalid column value. I'm wondering if possible the whole thing just corrupted? I'm going to try to scrap everything and restart.

Paul Newcome

Double check your ranges. I see that [Reason For Win] in the target sheet is producing that error, but I notice that {Reason For Winning} in the reference sheet isn't actually being referenced. I also see that only the top row in your reference sheet has the date being referenced as opposed to the entire column.

Keep in mind though, I don't think that your formula will produce the expected results even after you troubleshoot the error. I think what you need is an INDEX/MATCH instead.

=INDEX({Column To Pull Over}, MATCH([Card Reference #]@row, {Card Reference Column}, 0))

Katie.works

Figured it out, kinda……I was trying to have coding in 4 columns and somehow they got tied together so whenever I changed a reference in one column it updated in in all the other columns breaking it! So even when I did have the correct formula it was still breaking everything else. I deleted all the formulas and restarted and now I've got it going.

Paul Newcome

It sounds like maybe you were clicking on "Edit Reference" instead of creating new references from scratch.