Formula to return nth cell in a sequence with criteria

[graemestown]

https://www.dropbox.com/s/amwx4p2ofoau8w3/Example.xlsx?dl=0

1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2		Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week	Week
3		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
4	Bread						969	883	638	494	112	750	683	986	273	390	993	566	465	127	=Formula
5	Cake			320	508	306	833	100	361	53	364	323	844	873	113	575	963	415	66	557	=Formula
6	Pies									320	720	480	574	203	607	54	900	796	807	774	=Formula
7	Rolls			602	404	801	340	977	767	583	933	731	285	335	833	462	858	872	360	932	=Formula
8	Donuts	12	16	20	23	92	121	717	523	105	180	263	98	923	138	470	516	460	518	266	=Formula
		Criteria for formula:
	1	Ignores blank cells and allows for an 'if greater than' statement. For example it ignores all the bold cells because they are less than 50
	2	Then counts 8 cells from the start of the valid sequence
	3	Formula returns the value in the 8th cell, the result I want returned is italic & underlined
	4	Formula would be in column u which I could drag down and would work regardless of where the sequence starts

<colgroup><col width="64" style="width:48pt"> <col width="64" span="20" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="20" style="width:48pt"></colgroup><tbody></tbody>

 

[graemestown]

As usual, works perfectly.
Thanks again!

 

[DanteAmor]

As usual, works perfectly.
Thanks again!

I'm glad to help you. I appreciate your kind comments.

 

[graemestown]

I think your formula only looks at the greater than 50 criteria.
How would I add the 'if blank' so it looks at both possibilities (blank and >50)

 

[DanteAmor]

I think your formula only looks at the greater than 50 criteria.
How would I add the 'if blank' so it looks at both possibilities (blank and >50)

The blank cell is less than 50, it is not necessary to set the criteria

 

[graemestown]

Ok, I see what you mean. Thanks.

 

[DanteAmor]

You're welcome, let me know if you have any doubt.

 

[graemestown]

Hi Dante

Slightly different question this time. How do you look up a horizontal list into a vertical data table. Something to do with index and match, I just can't get it working.
The simple example looks like this.

I have a column with a list A,B,C,D,E,F,G with sales values against them

List	Sales
A	12
B	15
C	17
D	9
E	21
F	34
G	45

<colgroup><col><col></colgroup><tbody>
</tbody>

I then have the same list A,B,C,D,E,F,G running horizontally. How do I do a lookup from the horizontal list below into the vertical list above. So A would return 12 etc...
The answer/formula needs to be in the row above the list.

Answer	12	15	17	9	21	34	45
List	A	B	C	D	E	F	G

<colgroup><col><col span="6"><col></colgroup><tbody>
</tbody>

Thanks!

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:28.51px;" /><col style="width:39.92px;" /><col style="width:74.14px;" /><col style="width:52.28px;" /><col style="width:29.47px;" /><col style="width:29.47px;" /><col style="width:29.47px;" /><col style="width:20.91px;" /><col style="width:29.47px;" /><col style="width:29.47px;" /><col style="width:29.47px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">List</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Sales</td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Answer</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">12</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">15</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">17</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">9</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">21</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">34</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-style:italic; font-family:Verdana; font-size:9pt; text-align:right; ">45</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">A</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12</td><td > </td><td >List</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">B</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">C</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">17</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">D</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">E</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">21</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">F</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">34</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">G</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">45</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formul</td></tr><tr><td >E1</td><td >=VLOOKUP(E2,$A$1:$B$8,2,0)</td></tr></table></td></tr></table>

 

[graemestown]

Thanks, that was easy, I thought it was more complicated.
I didn't even try the v-lookup option, didn't think it would work like that.
Cheers!

 

[DanteAmor]

You're welcome.
Sometimes the easiest ones go away.