two formulas i'm trying to turn into one

[Stoobydoo]

Hi,

Both individual formulas work.

I've the following formula in F1 (an array), it looks at cell A1, finds matches in sheet 1 column A and the returns the SMALL value found in sheet 1 column B:

F1 =IFERROR(SMALL(IF((Sheet1!$A:$A=A1)*ISNUMBER(Sheet1!$B:$B),Sheet1!$B:$B),1),"")

Next I've a display formula in D1, it looks for the count of B1:C1 and if this count is greater or = 1 it returns the value in F1 else returns a blank:

D1 =IF(COUNTIF(B1:C1,">=1"),F1,"")

I'd like to add the formula of F1 into the formula in D1 or visa versa.

Help please.

[Pete_UK]

You could put this formula in D1:

=IF(COUNTIF(B1:C1,">=1"),IFERROR(SMALL(IF((Sheet1!$A:$A=A1)*ISNUMBER(Sheet1!$B:$B),Sheet1!$B:$B),1),""),"")

Note that this is an array formula, so needs to be committed using the key combination of Ctrl-Shift-Enter instead of the usual < Enter >

It is not a good idea to use complete column references in an array formula, so you might like to reduce the range of $A:$A and $B:$B where they occur.

Hope this helps.

Pete

[Stoobydoo]

Pete,

Many thanks. I will also change the range as advised.

Cheers, Stuart

[Pete_UK]

Thanks for the rep, Stuart.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete