INDEX-MATCH Showing Wrong Information

I am using the following to pull a course title from another sheet:

=IFERROR(INDEX({SubjectCourses-Titles-Credit}, MATCH([Subject + Course]31, {Subject+Course}), 3), "")

It has worked fine with several course codes (Subject + Course), but when I enter an incorrect course code (i.e., it doesn't exist, such as HOS 419), it displays the course title for HOS 417 (the course code that proceeds it in the other sheet).

If HOS 419 does not exist in the other sheet, shouldn't it not be a match? If I just use MATCH, it points to the row with HOS 417, it doesn't display a #NOMATCH.

{SubjectCourses-Titles-Credit} is a range in Sheet 1 (list of courses) that includes the course code (e.g, HOS 417), title, and credit hours. In Sheet 2, when I enter the course code ([Subject + Course]), it has always displayed the course title (column 3) by looking for that course code in column in Sheet 1 with course codes ({Subject+Course}).

HOS 419, if it existed, would appear in the row after that HOS 417 course. There are other courses above HOS 419 (e.g., HOS 421, HOS 434, etc.). But, there is no HOS 419 entry in Sheet 1, so I should not see a course title. (This is intended to be a way to flag incorrectly entered course codes in Sheet 2).

Find more posts tagged with

Sheets

Cell linking

Comments

Lauren B

Have you tried Index/Collect (Where {Sheet Name Range 1} is the Course Name column from your source sheet and {Sheet Name Range 2} is the Course Code column from your source sheet):

=INDEX(COLLECT({Source Sheet Name Range 1}, {Source Sheet Name Range 2}, [Course Code]1), 1)

I tried this out and I correctly got an "#INVALID VALUE" error when I queried a course code that did not exist in the parent sheet.

Wilbsguy

Match has 3 fields, the matching criteria (what to find), the matching range (where to find it), and the third is the type of match (what to do if it isn't there). 0 is what you want. It is an strict matching returning an error if something doesn't exist. 1 or -1 will go up or down the list from where it would sit if it existed I think. I haven't ever used it like that though so disclaimer.

Also, use @row instead of the row number. It takes a little work but I like being able to follow it a little better when I need to debug something.Helps keep things from getting jumbled if someone does something without thinking.

Oh, and you don't need the "3". Pull the range you need to look up, pull just the column you need to return. Then call those both in your formula. The 3 will break (at least in excel it does, I have no idea how SmartSheet works with multi-column ranges on other sheets is adjusted). I find I have fewer problems if I use the formulas in an efficient manner. So IFERROR(INDEX({Credit?},MATCH([Subject + Course]@row,{Subject+Course},0)),"") should be what you are looking for. My guess from the name is the third column is Credit and that is what you want returned.

Art Schneiderheinze

Thank you---that worked perfectly.

Art Schneiderheinze

I will explore the COLLECT function more. I did not know about this. Thanks!

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…