Match?, lookup?

Posted by Paul on September 24, 2001 1:33 PM

I need a formula in cell C2 on sheet 2 that will take the numbers in A1:A25 on sheet2 and look for them on sheet 1 in A:A, if it finds the number I need it to return the contents in sheet 1 C:C that corresponds to number in column A:A. Example if sheet 2 cell A1 =123, sheet 1 cell A25=123, sheet 1 cell C25=Closed,the formula in sheet 2 cell C2 should return Closed. If no number is found it should say No match. Thanks for your help!

Posted by Aladin Akyurek on September 24, 2001 1:48 PM

Paul,

In A1 enter in Sheet2: =IF(COUNTIF(Sheet1!A:A,A2),INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)),"No Match")

Copy down as far as needed.

Aladin

Posted by Ian on September 24, 2001 1:57 PM

Thanks for your help!

Is it likely that there will be more that 1 occurance of the number?

if the answer is no then

=IF(ISBLANK(A1),"",IF(ISERROR(VLOOKUP(A1,Sheet1!A:C,3,0)),"No Match",VLOOKUP(A1,Sheet1!A:C,3,0)))

in C2, copy down to C26

BUT why C2 not C1, when your referencing A1:A25

any good?

Ian

Posted by Ian Mac on September 24, 2001 2:08 PM

See I have no brains :) (NT)