Looking up a value from one cell in another range of cells

[tinyguppie]

I have a worksheet that has in column A a unique identifier. And then
for each of those idenitifers, an X in a column under a month.


A B C D

1 Nov Dec Jan
2 15 X
3 11 X
4 12 X
5 10 X
6 14 X
7 16 X
8 13 X


Then in a seperate worksheet in column A i have those same unique
identifiers as above.
I want column B to list the "Month" column that that identifier appears
in.

Basically a formula that looks finds the corresponding unique ID in the
above worksheer, finds the X in the same row, and then outputs the
date.

A B

1 10 Dec
2 11 Nov
3 12 Jan
4 13 Dec
5 14 Jan
6 15 Dec
7 16 Nov


Ie so in the above, Column B would be a formula that outputs the either
the contents of B1, C1, D1 from the first worksheet (ie the date
specified there), depending on where the X is.

Hope this makes sense!

Any help woudl be greatly appreciated!

 

[vezerid]

Hi,

This formula assumes that in Sheet1 months occupy the range B1:M1 and
the data is in rows 2:20.

=INDEX(Sheet1!$B$1:$M$1,MATCH("x",OFFSET(Sheet1!$B$1:$M$1,MATCH(A2,Sheet1!$A$2:$A$20,0),0)))

HTH
Kostis Vezerides

 

[Herbert Seidenberg]

Use
http://www.j-walk.com/ss/excel/usertips/tip068.htm
to reverse Pivot Table, then Filter by "X"