#NO MATCH when counting the results of a VLOOKUP

Bianca_ · 10/18/19

I am trying to count how many items fit 2 criteria, one is the year, the other is the state. The issue is coming in with the state, as I just changed it to a VLOOKUP and my formulas were all working before I did that. The VLOOKUP is working, but there are a few values which return a #NO MATCH and I can't change that due to the nature of those particular rows.

I am now getting a #NO MATCH error for all of my COUNTIFS formulas.

I saw this post <https://community.smartsheet.com/discussion/errors> and it seems like a similar problem, but the solution didn't work for me. I'm not sure if I'm implementing it wrong?

Here is my formula:

=COUNTIFS([Project Year]:[Project Year], "2019", State:State, IFERROR(@cell, 0) = "AL")

Any help is appreciated! I've been staring at this all day!

Paul Newcome · 10/21/19

You are going to have to remove the error from the column being counted. Why is it that you cannot change the "#NO MATCH"?

Bianca_ · 10/21/19

Found a solution! I changed it so that the VLOOKUP utilizes IFERROR instead of the COUNTIF and it works! Going to leave this up in case anyone experiences a similar issue.

My above formula is now

=COUNTIFS([Project Year]:[Project Year], "2019", State:State, "GA")

and my VLOOKUP in the State column is

=IFERROR(VLOOKUP([Location Name]105, {Location Info Range 1}, 4, false), "N/A")

Paul Newcome · 10/22/19

Well done. That's why I was asking about why you couldn't change the #NO MATCH error.

#NO MATCH when counting the results of a VLOOKUP

Comments

Help Article Resources

Categories