ExcelBanter - COUNTIF and VLOOKUP in one formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - COUNTIF and VLOOKUP in one formula (https://www.excelbanter.com/excel-discussion-misc-queries/266458-countif-vlookup-one-formula.html)

COUNTIF and VLOOKUP in one formula

Using the attached table as an example....

I'm using a VLOOKUP formula to return the values in column I, 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 column K 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.

Can anyone help? I've been stuck on this for days.

Quote:

Originally Posted by Stepper (Post 960223)

Using the attached table as an example....

I'm using a VLOOKUP formula to return the values in column I, 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 column K 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.
Can anyone help? I've been stuck on this for days.

What kind of data are you intended to show in column I ?
Could you explain with a little more details the search are you using?

You can control the number of cells with values.

Look at this example: I put the media on I1 if there are 3 or more values on tehe B1:H1 interval

I1 -- =IF(COUNTIF(B1:H1,"<0")=3,(SUM(B1:H1)/COUNTIF(B1:H1,"<0")),"NA")

Tell me your necesity a little better, Please.