Lookup Value and find Corresponding Value on another row same column

M

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 :confused:
 
D

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)
 
R

RagDyer

Dave,

Honest question.
Any particular reason for using an array?

=INDEX(A1:AE1,MATCH("X",A2:AE2,0))
 
D

Dave Peterson

Nope. Just thougth of that first.

To the OP. RD's formula is better.
Dave,

Honest question.
Any particular reason for using an array?

=INDEX(A1:AE1,MATCH("X",A2:AE2,0))
Click to expand...
 
Top