Lookup Value and find Corresponding Value on another row same column

[martialtiger]

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

[Dave Peterson]

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]

Worked like a charm! Thanks Dave!

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

[RagDyer]

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" <petersod@verizonXSPAM.net> wrote in message
news:4370EA3D.D49BAA19@verizonXSPAM.net...
> 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]

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" <petersod@verizonXSPAM.net> wrote in message
> news:4370EA3D.D49BAA19@verizonXSPAM.net...
> > 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