VLOOKUP and COUNTIF in the same formula

Using the attached table as an example....

I'm using a VLOOKUP formula to return the values in column J
=VLOOKUP("Bob Smith",A1:J4,10,FALSE)

However, I only want the values returned if there are 3 or more values in columns B-H. If there are less than 3, I want "NA" returned.

I've tried a few variations, including putting a COUNTIF formula in another column to determine whether there are 3 or more values, then incorporating that column into the VLOOKUP, but I can't get this to work either.

I was given this formula, which works for the second part of the process (returning the value in J only if there are 3 or more values in B-H), but does not incorporate the VLOOKUP.
=IF(COUNTIF(B1:H1,">0")>=3,J2,"NA")

I tried this: =VLOOKUP($A6,A1:J4(IF(COUNTIF(B:H,">0")>=3,J,"NA")),10,FALSE)
But it refers to columns without rows, which I don't think is right, and it returned a Circular Reference error anyway.

Can anyone help?

Add a column K with

=IF(COUNTIF(B1:H1,">0")>=3,J1,NA())

copied down and change your vlookup to return column 11 of A1:K4

=VLOOKUP("Bob Smith",A1:K4,11,FALSE)

You could hide column K.

I suppose that column J is a calculated value in your real workbook, averaging the previous columns. You could use your existing formula in column J encased in an IF statement

=IF(COUNTIF(B1:H1,">0")>=3,<your existing formula>,NA())

and then use your original vlookup.

In terms of single cell - just use INDEX/MATCH within the COUNTIF:

Please Login or Register  to view this content.