Hi, I have an excel formula that searches multiple columns on one spreadsheet and when the condition is met it displays the data from another on a second tab. This part of the formula works: =IF(COUNTIF('All IFC'!F85:AS85,"No")=40,'All IFC'!A85, 0)

I'm having trouble making it also display the adjacent data in Column C. I thought using this would work: =IF(COUNTIF('All IFC'!F85:AS85,"No")=40,'All IFC'!A85:C85, 0), but i am not getting results.

Would anyone know where my error is?

thanks :)

I'm not sure what you are trying to do with the second formul. The first formula appears to be pretty straightforward:

If every cell in F85:AS85 contains "No", return the value in A85, if not, return 0.

Your second function basically does the same thing, but it depends on where you put the formula.

When you tell Excel to return a value from a

rangeof cells (e.g. A85:C85) it knows that it cannot put more than one value in a single cell, so what it returns depends on where you put the formula.If you put the formula in a column that is within the range (e.g. A, B, or C) it will return the value from that Column. In other words, if you put that formula in B10, it would return the value from B85. C10 would get you C85.

However, if you put the formula in a column that is not within the range, (e.g. Column D or beyond), you'll get a #VALUE error.

Try it!

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ask Your Question

Weekly Poll