the result on Col.B seems unusual...

What result did you get that's unusual?

When I change C2:E2 to X1, Y1, Z1 I still get the expected results.

The formula works for what it was designed for. You may be expecting it to
do things that it isn't intended to do!

Biff

"driller" wrote in message
...
Almost got it Biff,,,but when i try to change
C2: X into X1,
D2: y into Y1,
E2: Z into Z1,
the result on Col.B seems unusual...
pls. try again ! thanks....more power
"Biff" wrote:

The probelm is this portion:

(F1&G1&H1)+0

Where the values are the text entries:

X y Z

The math operation of adding 0 causes the #VALUE! error.

(XyZ)+0 = #VALUE!

Change this:

(F1&G1&H1)+0

To:

IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0)

Biff

"driller" wrote in message
...
Almost got it !

from previous question of Luke "Array Help" yesterday, where the
checked
answer is :
on B1
=IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"")

Please reply for an answer that is good for the following arrangement.

A B C D E F G H
1 1 * 0 3 5 9
2 X y Z 7 4 4
3 100 9 8 5 1 0 0
4 2 1 3
5 6 4 3
6 #value! X y Z
7 1 6 4
8 #value! z Z *
9 4 4 4
10 950 9 5 0
11 #value! X x Z

I hope it's easy to modify for advance learning...thanks...