software

Hi,

Trying to work out how to get the Calc to check if the value of B1 is in
an column C1 to C3000, then return that value in A1.

I have tried to use MATCH but when i put the equation into the rows
below, it incriments the cells so the formula for row 2 would be

=MATCH(B2,C2:C3001)

I need the formula to be

=MATCH(B2,C1:C3000)
Rich

Hi!

Try this:

=MATCH(B1,C$1:C$3000)

Copy down as needed.

Biff

quot;richquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Trying to work out how to get the Calc to check if the value of B1 is in
gt; an column C1 to C3000, then return that value in A1.
gt;
gt; I have tried to use MATCH but when i put the equation into the rows below,
gt; it incriments the cells so the formula for row 2 would be
gt;
gt; =MATCH(B2,C2:C3001)
gt;
gt; I need the formula to be
gt;
gt; =MATCH(B2,C1:C3000)
gt;
gt;
gt;
gt; Rich
Biff wrote:
gt; Hi!
gt;
gt; Try this:
gt;
gt; =MATCH(B1,C$1:C$3000)
gt;
gt; Copy down as needed.
gt;
gt; Biff
gt;
gt; quot;richquot; gt; wrote in message
gt; ...
gt;
gt;gt;Hi,
gt;gt;
gt;gt;Trying to work out how to get the Calc to check if the value of B1 is in
gt;gt;an column C1 to C3000, then return that value in A1.
gt;gt;
gt;gt;I have tried to use MATCH but when i put the equation into the rows below,
gt;gt;it incriments the cells so the formula for row 2 would be
gt;gt;
gt;gt;=MATCH(B2,C2:C3001)
gt;gt;
gt;gt;I need the formula to be
gt;gt;
gt;gt;=MATCH(B2,C1:C3000)
gt;gt;
gt;gt;
gt;gt;
gt;gt;Rich
gt;
gt;
gt;

Thanks, that worked, but now i need to get it to return the matched
string to the cell rather than the number of matches...

so if the contents of B1 was 1001 , then i would like the A1 to get 1001
in it as well (but only if matched to a value in C1:C3000)

Clear as mud, i'm sure but cannot think of a better way to put it.
hi!

try this!

in A1:

=IF(B1=quot;quot;,quot;quot;,IF(ISNA(INDEX($B$1:$B$5,MATCH(B1,$C$1 :$C$5,0))),quot;quot;,INDEX($B$1:$B$5,MATCH(B1,$C$1:$C$5,0 ))))

and copy down upto A5,
assuming that your data in A1:C5

-via135gt; Thanks, that worked, but now i need to get it to return the matched
gt; string to the cell rather than the number of matches...
gt;
gt; so if the contents of B1 was 1001 , then i would like the A1 to get
gt; 1001
gt; in it as well (but only if matched to a value in C1:C3000)
gt;
gt; Clear as mud, i'm sure but cannot think of a better way to put it.--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=539610Try this in A1:

=IF(ISNUMBER(MATCH(B1,C$1:C$3000,0)),B1,quot;quot;)

Biff

quot;richquot; gt; wrote in message
...
gt; Biff wrote:
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =MATCH(B1,C$1:C$3000)
gt;gt;
gt;gt; Copy down as needed.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;richquot; gt; wrote in message
gt;gt; ...
gt;gt;
gt;gt;gt;Hi,
gt;gt;gt;
gt;gt;gt;Trying to work out how to get the Calc to check if the value of B1 is in
gt;gt;gt;an column C1 to C3000, then return that value in A1.
gt;gt;gt;
gt;gt;gt;I have tried to use MATCH but when i put the equation into the rows
gt;gt;gt;below, it incriments the cells so the formula for row 2 would be
gt;gt;gt;
gt;gt;gt;=MATCH(B2,C2:C3001)
gt;gt;gt;
gt;gt;gt;I need the formula to be
gt;gt;gt;
gt;gt;gt;=MATCH(B2,C1:C3000)
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;Rich
gt;gt;
gt;gt;
gt;gt;
gt;
gt; Thanks, that worked, but now i need to get it to return the matched string
gt; to the cell rather than the number of matches...
gt;
gt; so if the contents of B1 was 1001 , then i would like the A1 to get 1001
gt; in it as well (but only if matched to a value in C1:C3000)
gt;
gt; Clear as mud, i'm sure but cannot think of a better way to put it.
gt;