How do i "lock" an array...or something...

[rich]

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

[Biff]

Hi!

Try this:

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

Copy down as needed.

Biff

"rich" <[email protected]> wrote in message
news:[email protected]...
> 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

[rich]

Biff wrote:
> Hi!
>
> Try this:
>
> =MATCH(B1,C$1:C$3000)
>
> Copy down as needed.
>
> Biff
>
> "rich" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>
>
>

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.

[via135]

hi!

try this!

in A1:

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

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

-via135

assuming that data is in B1:C5

-via135

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.

[Biff]

Try this in A1:

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

Biff

"rich" <[email protected]> wrote in message
news:[email protected]...
> Biff wrote:
>> Hi!
>>
>> Try this:
>>
>> =MATCH(B1,C$1:C$3000)
>>
>> Copy down as needed.
>>
>> Biff
>>
>> "rich" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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
>>
>>
>>
>
> 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.
>