On Feb 9, 6:11 pm, "Fin Fang Foom" wrote:
On Feb 9, 6:04 pm, Domenic wrote:



I tried using LOOKUP instead of INDEX but unfortunately it too doesn't
like the array defined by Col_A. It returns #N/A. Here's the formula I
tried...

=LOOKUP(SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2)),S ,Col_A)

...confirmed with CONTROL+SHIFT+ENTER.

In article ,

Domenic wrote:
Thanks Harlan! My apologies. I forgot to test it. It looks like INDEX
will not accept the array, in either case. Any ideas?

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
...
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
...

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

Thank you Domenic. I'll try it right now and post back.


Domenic,

Did the formula you provied work for you? I could not get it to work.