formula error but only with one specific data

WinaHath

it's strange but the formula works on all other rows except this one - Dept Code 1000

=INDEX({Functions}, MATCH([Select the Department Code for this request]@row, {Functions Range 2}, 1))

Thanks for any help!😀

JamesB

@WinaHath

Do you have a department code 1000 in the reference sheet? If not, then your equation is ending in an Error, hence the #NO MATCH. To correct for this, you could run the equation using an IF(ISERROR formula to get the result to be whatever you want when there is no matching Functions.

=IF(ISERROR(INDEX({Functions}, MATCH([Select the Department Code for this request]@row, {Functions Range 2}, 1))),"CUSTOM RESULT",INDEX({Functions}, MATCH([Select the Department Code for this request]@row, {Functions Range 2}, 1)))

WinaHath

I do have that department on the reference sheet.

Cory Page

@WinaHath It might be a stretch but I have run into issues where one cell is text and the other is a number in which case I receive a no match.

If you double click on the Match values look and see if one has a '1000 and then check and see if the other doesn't. Might not be your issue but it kind of seems like that might be the case.