In an excel sheet, say we have some set values in A2 to A50 and B2 to B50 and
C2 to C29. Now we need to compare say A30 onwards to cell values A2 to A29
and B30 onwards to B2 to B29 and if both values found then the row in which
both value found, the value in column C will be the value in the C column of
cells C30 onwards. Hope this example explains the problem clearly.

One way ..

Put in the formula bar for C30,
and array-enter, i.e. press CTRL+SHIFT+ENTER:

=IF(ISNA(MATCH(A30&"_"&B30,$A$2:$A$29&"_"&$B$2:$B$ 29,0)),"",INDEX($C$2:$C$29
,MATCH(A30&"_"&B30,$A$2:$A$29&"_"&$B$2:$B$29,0)))

Copy C30 down to C50

Unmatched cases, if any, will return blanks: "" instead of #N/As
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Man" wrote in message
...
In an excel sheet, say we have some set values in A2 to A50 and B2 to B50
and
C2 to C29. Now we need to compare say A30 onwards to cell values A2 to A29
and B30 onwards to B2 to B29 and if both values found then the row in
which
both value found, the value in column C will be the value in the C column
of
cells C30 onwards. Hope this example explains the problem clearly.

Is it just?

=IF(AND(A30=A2,B30=B2),C2,""_

and copy down.

--
HTH

Bob Phillips

"Man" wrote in message
...
In an excel sheet, say we have some set values in A2 to A50 and B2 to B50
and
C2 to C29. Now we need to compare say A30 onwards to cell values A2 to A29
and B30 onwards to B2 to B29 and if both values found then the row in
which
both value found, the value in column C will be the value in the C column
of
cells C30 onwards. Hope this example explains the problem clearly.

Thanks Max ... formula works perfectly ...thanks a ton !!!

"Max" wrote:

One way ..

Put in the formula bar for C30,
and array-enter, i.e. press CTRL+SHIFT+ENTER:

=IF(ISNA(MATCH(A30&"_"&B30,$A$2:$A$29&"_"&$B$2:$B$ 29,0)),"",INDEX($C$2:$C$29
,MATCH(A30&"_"&B30,$A$2:$A$29&"_"&$B$2:$B$29,0)))

Copy C30 down to C50

Unmatched cases, if any, will return blanks: "" instead of #N/As
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Man" wrote in message
...
In an excel sheet, say we have some set values in A2 to A50 and B2 to B50
and
C2 to C29. Now we need to compare say A30 onwards to cell values A2 to A29
and B30 onwards to B2 to B29 and if both values found then the row in
which
both value found, the value in column C will be the value in the C column
of
cells C30 onwards. Hope this example explains the problem clearly.

Sorry Bob, guess I was not able to explaing my problem correctly. Let me try
it again, I want to compare all the rows above 30 with all the rows following
30 for columns A and B. Whichever cell matches the value in column C has to
be written to the column C of the row 30 and greater.
Anyways, the posting by Max has the solution to my question. Thank you for
replying.

"Bob Phillips" wrote:

Is it just?

=IF(AND(A30=A2,B30=B2),C2,""_

and copy down.

--
HTH

Bob Phillips

"Man" wrote in message
...
In an excel sheet, say we have some set values in A2 to A50 and B2 to B50
and
C2 to C29. Now we need to compare say A30 onwards to cell values A2 to A29
and B30 onwards to B2 to B29 and if both values found then the row in
which
both value found, the value in column C will be the value in the C column
of
cells C30 onwards. Hope this example explains the problem clearly.

Glad to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Man" wrote in message
...
Thanks Max ... formula works perfectly ...thanks a ton !!!