| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Lookup Value and find Corresponding Value on another row same column
MS Excel 2003 Let's say I have row 1 containing values 1-31 (so the value in A1 is 1, B1 is 2, etc all the way until 31). Now in row 2 is where I would put an "X" in any of the columns that contain a number from 1-31. How can I create a formula that will give me the value (1-31) from row 1 depending on where I place X in row 2? TIA  -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php...fo&userid=4743 View this thread: http://www.excelforum.com/showthread...hreadid=483202 |
|
#2
|
|||
|
|||
|
Lookup Value and find Corresponding Value on another row same column
One way:
=INDEX(A1:AE1,MATCH(TRUE,A2:AE2="x",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way: =MIN(IF(A2:AE2="x",COLUMN(A2:AE2))) (still an array formula) martialtiger wrote: MS Excel 2003 Let's say I have row 1 containing values 1-31 (so the value in A1 is 1, B1 is 2, etc all the way until 31). Now in row 2 is where I would put an "X" in any of the columns that contain a number from 1-31. How can I create a formula that will give me the value (1-31) from row 1 depending on where I place X in row 2? TIA -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php...fo&userid=4743 View this thread: http://www.excelforum.com/showthread...hreadid=483202 -- Dave Peterson |
|
#3
|
|||
|
|||
|
Lookup Value and find Corresponding Value on another row same column
Worked like a charm! Thanks Dave! Dave Peterson Wrote: One way: =INDEX(A1:AE1,MATCH(TRUE,A2:AE2="x",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way: =MIN(IF(A2:AE2="x",COLUMN(A2:AE2))) (still an array formula) martialtiger wrote: MS Excel 2003 Let's say I have row 1 containing values 1-31 (so the value in A1 is 1, B1 is 2, etc all the way until 31). Now in row 2 is where I would put an "X" in any of the columns that contain a number from 1-31. How can I create a formula that will give me the value (1-31) from row 1 depending on where I place X in row 2? TIA -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php...fo&userid=4743 View this thread: http://www.excelforum.com/showthread...hreadid=483202 -- Dave Peterson -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php...fo&userid=4743 View this thread: http://www.excelforum.com/showthread...hreadid=483202 |
|
#4
|
|||
|
|||
|
Lookup Value and find Corresponding Value on another row same column
Dave,
Honest question. Any particular reason for using an array? =INDEX(A1:AE1,MATCH("X",A2:AE2,0)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... One way: =INDEX(A1:AE1,MATCH(TRUE,A2:AE2="x",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way: =MIN(IF(A2:AE2="x",COLUMN(A2:AE2))) (still an array formula) martialtiger wrote: MS Excel 2003 Let's say I have row 1 containing values 1-31 (so the value in A1 is 1, B1 is 2, etc all the way until 31). Now in row 2 is where I would put an "X" in any of the columns that contain a number from 1-31. How can I create a formula that will give me the value (1-31) from row 1 depending on where I place X in row 2? TIA -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php...fo&userid=4743 View this thread: http://www.excelforum.com/showthread...hreadid=483202 -- Dave Peterson |
|
#5
|
|||
|
|||
|
Lookup Value and find Corresponding Value on another row same column
Nope. Just thougth of that first.
To the OP. RD's formula is better. RagDyer wrote: Dave, Honest question. Any particular reason for using an array? =INDEX(A1:AE1,MATCH("X",A2:AE2,0)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... One way: =INDEX(A1:AE1,MATCH(TRUE,A2:AE2="x",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way: =MIN(IF(A2:AE2="x",COLUMN(A2:AE2))) (still an array formula) martialtiger wrote: MS Excel 2003 Let's say I have row 1 containing values 1-31 (so the value in A1 is 1, B1 is 2, etc all the way until 31). Now in row 2 is where I would put an "X" in any of the columns that contain a number from 1-31. How can I create a formula that will give me the value (1-31) from row 1 depending on where I place X in row 2? TIA -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php...fo&userid=4743 View this thread: http://www.excelforum.com/showthread...hreadid=483202 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
