Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i "lock" an array...or something...
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i "lock" an array...or something...
Hi!
Try this: =MATCH(B1,C$1:C$3000) Copy down as needed. Biff "rich" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i "lock" an array...or something...
Biff wrote:
Hi! Try this: =MATCH(B1,C$1:C$3000) Copy down as needed. Biff "rich" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i "lock" an array...or something...
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 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 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=539610 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i "lock" an array...or something...
Try this in A1:
=IF(ISNUMBER(MATCH(B1,C$1:C$3000,0)),B1,"") Biff "rich" wrote in message ... Biff wrote: Hi! Try this: =MATCH(B1,C$1:C$3000) Copy down as needed. Biff "rich" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Array with Array | Excel Worksheet Functions | |||
Pass an array to Rank | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |