Index and Match: I dont HAVE an error message, I WANT one.

**dasafacjack** · 12-10-2011, 01:34 PM

Hello everyone,

First post.

I want my Index and Match formula to give me an error if it returns more than one result. I do not want it to generate a table with the extra results or anything that keeps the extra data. I want one result. I understand the 0 or 1 at the end of the Match criteria, do not want it. I want an error. I am sure that is not said too much around here.

The next best thing would be for the multiple data to be listed in a single sell, not a table.

Is there a fix or can anyone point me in the direction of a function that I can use. IT HAS TO BE PART of the existing formula because I am running about 50 individual index and match functions that are in need of the same error checking.

Thank you in advance.

**dasafacjack** · 12-10-2011, 01:35 PM

*correction "I do NOT want it to generate a table..."

**shg** · 12-10-2011, 01:51 PM

What's the formula you're using now?

**dasafacjack** · 12-10-2011, 02:00 PM

{=IFERROR(INDEX('Sheet2'!$A$2:A$401,MATCH("YES",IF('Sheet2'!$J$2:$J$401='Sheet1'!$A11,'Sheet1'!Q$2:Q $401,""),0)),"")}

**shg** · 12-10-2011, 02:06 PM

Maybe ...

=IF(COUNTIFS(Sheet2!$J$2:$J$401, Sheet1!$A11, Sheet1!Q$2:Q$401, "YES") <> 1, "Oops",
INDEX(Sheet2!$A$2:A$401, MATCH("YES", IF(Sheet2!$J$2:$J$401=Sheet1!$A11, Sheet1!Q$2:Q$401), 0)))