a CHALLANGE using OR in an array formula
 

I'm using the following formula and now need to look for data matching CCYI
or CCYV. I've tried to use the OR but it returns a wrong answer.

{=INDEX('E373'!$T$1:$T$2000,SMALL(IF(('E373'!$Y$1: $Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000="CCYI"),ROW('E373' !$T$1:$T$2000)),ROW('E373'!$T$1:$T$2000)))}

I'd pull the rest of my hair out if I had any!

Your help is greatly appreciated,
O'C

Try this...

{=INDEX('E373'!$T$1:$T$2000,SMALL(IF(('E373'!$Y$1: $Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000="CCYI"),ROW('E373' !$T$1:$T$2000),IF(('E373'!$Y$1:
$Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000="CCYV"),ROW('E373' !$T$1:$T$2000))),ROW('E373'!$T$1:$T$2000)))}

It is not as nice as an OR statement, but it should receive the same
result.


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=319918

{=INDEX('E373'!$T$1:$T$2000,SMALL(IF(('E373'!$Y$1: $Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000={"CCYI","CCYV"}),R OW('E373'!$T$1:$T$2000)),ROW('E373'!$T$1:$T$2000)) )}

Hope this helps!

O'C Wrote:
I'm using the following formula and now need to look for data matching
CCYI
or CCYV. I've tried to use the OR but it returns a wrong answer.

{=INDEX('E373'!$T$1:$T$2000,SMALL(IF(('E373'!$Y$1: $Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000="CCYI"),ROW('E373' !$T$1:$T$2000)),ROW('E373'!$T$1:$T$2000)))}

I'd pull the rest of my hair out if I had any!

Your help is greatly appreciated,
O'C


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=319918

sir,
pls help me about this function. that it displya the numeric value in thai
language buthow it can be poosible that this function or any substitute can
display the numeric value in english language.

You've been given an answer at least 5 times in the last week, and
you'll get the same answer no matter how many times you continue to
post. Bahtext() does not have an equivalent in English. You'll need to
use a macro.


In article ,
Rao Ratan Singh wrote:

sir,
pls help me about this function. that it displya the numeric value in thai
language buthow it can be poosible that this function or any substitute can
display the numeric value in english language.

Thanks Everyone!!!

Domenic those brackets worked like a champ. Where do you guys learn this
stuff??? I must spend hours in the help and using trial and error methods.
I get smarter every time I use this WEB assistance, I think I'll just start
here in the future!!!

Thanks,
O'C

"Domenic" wrote:


{=INDEX('E373'!$T$1:$T$2000,SMALL(IF(('E373'!$Y$1: $Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000={"CCYI","CCYV"}),R OW('E373'!$T$1:$T$2000)),ROW('E373'!$T$1:$T$2000)) )}

Hope this helps!

O'C Wrote:
I'm using the following formula and now need to look for data matching
CCYI
or CCYV. I've tried to use the OR but it returns a wrong answer.

{=INDEX('E373'!$T$1:$T$2000,SMALL(IF(('E373'!$Y$1: $Y$2000='STATUS
SHEET'!B3)*('E373'!$Q$1:$Q$2000="CCYI"),ROW('E373' !$T$1:$T$2000)),ROW('E373'!$T$1:$T$2000)))}

I'd pull the rest of my hair out if I had any!

Your help is greatly appreciated,
O'C


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=319918