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

 

[shg]

note you will get zero's for the empty cells with shg's version ...

Add & "" to the end.

 

[Akashwani]

Hi,

Thanks Aladin, once again, you have shown me that there is always more than one way to skin the Excel cat! Thanks.

circledchicken, that was my formula that I tried and which didn't work!

I'll probaly use CF to "hide" the zero's as my brain is closing down and a solution wont spring to mind.

Thanks again to everyone for their time.

Ak