Lookup brain freeze!!

[Akashwani]

Hi,

I have the following situation....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Red	Data1	Blue	Data2	Green	Data2	Pink	Data3	Yellow	Data4		Lookup	Results
2	Red1	v	Blue1	W	Green1	x	Pink1	Y	Yellow1	z		Blue	Blue1
3	Red2	v	Blue2	W	Green2	x	Pink2	Y	Yellow2	z			Blue2
4	Red3	v	Blue3	W	Green3	x	Pink3	Y	Yellow3	z			Blue3
5	Red4	v	Blue4	W	Green4	x	Pink4	Y	Yellow4	z			Blue4
6	Red5	v	Blue5	W	Green5	x	Pink5	Y	Yellow5	z			Blue5
7	Red6	v	Blue6	W	Green6	x	Pink6	Y	Yellow6	z			Blue6
8	Red7	v	Blue7	W	Green7	x			Yellow7	z			Blue7
9			Blue8	W	Green8	x			Yellow8	z			Blue8
10			Blue9	W					Yellow9	z			Blue9
11			Blue10	W									Blue10
12
Sheet1

Which formula do I require in M2 (copied down).

I'm sure I have had a similar situation in the past, but I cannot find any reference to it in my "sample workbooks"!!

Thanks

Ak

 

[Aladin Akyurek]

What is the desired outcome?

 

[shg]

Isn't something missing? What is supposed to select the row?

 

[etaf]

just make sure I understand

you type a colour in L2
and then you want to lookup the colour across the range
A1:J1
and find the same word (colour) in the header and than in the column copy all the values under that heading

 

[Akashwani]

Hi,

The Lookup value is the column header Blue (L2) and the outcome are the cells below Blue (M2:M11 in the example above)

** Yes etaf**

***Additional example***

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Red	Data1	Blue	Data2	Green	Data2	Pink	Data3	Yellow	Data4		Lookup	Results
2	Red1	v	Blue1	W	Green1	x	Pink1	Y	Yellow1	z		Pink	Pink1
3	Red2	v	Blue2	W	Green2	x	Pink2	Y	Yellow2	z			Pink2
4	Red3	v	Blue3	W	Green3	x	Pink3	Y	Yellow3	z			Pink3
5	Red4	v	Blue4	W	Green4	x	Pink4	Y	Yellow4	z			Pink4
6	Red5	v	Blue5	W	Green5	x	Pink5	Y	Yellow5	z			Pink5
7	Red6	v	Blue6	W	Green6	x	Pink6	Y	Yellow6	z			Pink6
8	Red7	v	Blue7	W	Green7	x			Yellow7	z
9			Blue8	W	Green8	x			Yellow8	z
10			Blue9	W					Yellow9	z
11			Blue10	W
12
Sheet1

Ak

 

[shg]

Post was truncated at right ...

Select M2:M11,

=INDEX($A$2:$J$11, 0, MATCH(L2, $A$1:$J$1, 0))

 

[circledchicken]

Hi,

Perhaps:

=LOOKUP(
    REPT("z", 255),
    CHOOSE(
      {1,2},
      "",
      INDEX(
        A$2:J$11,
        ROWS(M$1:M1),
        MATCH(L$2, A$1:J$1, 0))))

 

[Akashwani]

Hi,

Thanks all for your time and replies.

I'm going with Shg's formula, partly becasue I almost had it myself, doh!!

=INDEX($A$1:$J$1,MATCH($L$2,$A$2:$J$11,0))

Thanks again folks, greatly appreciated as always.

Ak

 

[Aladin Akyurek]

M2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$J$11,
  SMALL(IF(1-(INDEX($A$2:$J$11,0,MATCH($L$2,$A$1:$J$1,0))=""),
  ROW($A$2:$J$11)-ROW($A$2)+1),ROWS($M$2:M2)),
  MATCH($L$2,$A$1:$J$1,0)),"")

 

[circledchicken]

Hi,

Thanks all for your time and replies.

I'm going with Shg's formula, partly becasue I almost had it myself, doh!!

=INDEX($A$1:$J$1,MATCH($L$2,$A$2:$J$11,0))

Thanks again folks, greatly appreciated as always.

Ak

I don't think your version will work based on what you described - unless its just a typo as shg's original array formula in post 6 does work.
Also note you will get zero's for the empty cells with shg's version so you may want to adjust for that or you may not mind that.