help on a formula (match and etc..)

[Coskun Ist]

Hello Excel Gurus,

At work, for tracing the production status of a product , we fill the cell with" Y" in accompanying table.

I want to write a formula in cell B11: Written in cell A11 the name of product, the cell will show its status.
Logic will work as follows.

1 - It will find the product1 row number (4).
2 - From this row, it searches the column who has "Y" value (C).
3 - if in column B, it returns Status 1, if in column C returns status2 etc..

I sent the sample file attached. I'd appreciate it if you could help.

I already thought something with MATCH function, but couldn't apply it.

Thanks in advance,

good evening.

SAMPLE TABLE:


A B A B A B
1 STATUS1 STATUS2 STATUS3 STATUS4
2 PRODUCT1 Y
3 PRODUCT2 Y
4 PRODUCT3 Y
5 PRODUCT4 Y
6
7
8
9
10
11 PRODUCT3 STATUS2

 

[Claus Busch]

Hi,

Am Sat, 12 Jan 2013 13:25:13 -0800 (PST) schrieb Coskun Ist:

A B A B A B
1 STATUS1 STATUS2 STATUS3 STATUS4
2 PRODUCT1 Y
3 PRODUCT2 Y
4 PRODUCT3 Y
5 PRODUCT4 Y
6
7
8
9
10
11 PRODUCT3 STATUS2

try:
=INDEX(A1:E1,1,MATCH("",INDIRECT(MATCH(A11,A1:A10,0)&":"&MATCH(A11,A1:A10,0)),-1))


Regards
Claus Busch