INDEX-MATCH Showing Wrong Information

Art Schneiderheinze

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).

 

 

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!