Formula to return nth cell in a sequence with criteria

G

graemestown

New Member
Joined
May 10, 2019
Messages
20
Office Version
  1. 365
Platform
  1. Windows
https://www.dropbox.com/s/amwx4p2ofoau8w3/Example.xlsx?dl=0

[TABLE="width: 1408"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="20" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl71, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl65, width: 64"]M[/TD]
[TD="class: xl65, width: 64"]N[/TD]
[TD="class: xl65, width: 64"]O[/TD]
[TD="class: xl65, width: 64"]P[/TD]
[TD="class: xl65, width: 64"]Q[/TD]
[TD="class: xl65, width: 64"]R[/TD]
[TD="class: xl65, width: 64"]S[/TD]
[TD="class: xl65, width: 64"]T[/TD]
[TD="class: xl65, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl66"]Bread[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]969[/TD]
[TD="class: xl65"]883[/TD]
[TD="class: xl65"]638[/TD]
[TD="class: xl65"]494[/TD]
[TD="class: xl65"]112[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]683[/TD]
[TD="class: xl72"]986[/TD]
[TD="class: xl65"]273[/TD]
[TD="class: xl65"]390[/TD]
[TD="class: xl65"]993[/TD]
[TD="class: xl65"]566[/TD]
[TD="class: xl65"]465[/TD]
[TD="class: xl65"]127[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]5[/TD]
[TD="class: xl66"]Cake[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]320[/TD]
[TD="class: xl65"]508[/TD]
[TD="class: xl65"]306[/TD]
[TD="class: xl65"]833[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]361[/TD]
[TD="class: xl65"]53[/TD]
[TD="class: xl72"]364[/TD]
[TD="class: xl65"]323[/TD]
[TD="class: xl65"]844[/TD]
[TD="class: xl65"]873[/TD]
[TD="class: xl65"]113[/TD]
[TD="class: xl65"]575[/TD]
[TD="class: xl65"]963[/TD]
[TD="class: xl65"]415[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]557[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]6[/TD]
[TD="class: xl66"]Pies[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]320[/TD]
[TD="class: xl65"]720[/TD]
[TD="class: xl65"]480[/TD]
[TD="class: xl65"]574[/TD]
[TD="class: xl65"]203[/TD]
[TD="class: xl65"]607[/TD]
[TD="class: xl65"]54[/TD]
[TD="class: xl72"]900[/TD]
[TD="class: xl65"]796[/TD]
[TD="class: xl65"]807[/TD]
[TD="class: xl65"]774[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]7[/TD]
[TD="class: xl66"]Rolls[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]602[/TD]
[TD="class: xl65"]404[/TD]
[TD="class: xl65"]801[/TD]
[TD="class: xl65"]340[/TD]
[TD="class: xl65"]977[/TD]
[TD="class: xl65"]767[/TD]
[TD="class: xl65"]583[/TD]
[TD="class: xl72"]933[/TD]
[TD="class: xl65"]731[/TD]
[TD="class: xl65"]285[/TD]
[TD="class: xl65"]335[/TD]
[TD="class: xl65"]833[/TD]
[TD="class: xl65"]462[/TD]
[TD="class: xl65"]858[/TD]
[TD="class: xl65"]872[/TD]
[TD="class: xl65"]360[/TD]
[TD="class: xl65"]932[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]8[/TD]
[TD="class: xl66"]Donuts[/TD]
[TD="class: xl73"]12[/TD]
[TD="class: xl73"]16[/TD]
[TD="class: xl73"]20[/TD]
[TD="class: xl73"]23[/TD]
[TD="class: xl70"]92[/TD]
[TD="class: xl70"]121[/TD]
[TD="class: xl70"]717[/TD]
[TD="class: xl70"]523[/TD]
[TD="class: xl70"]105[/TD]
[TD="class: xl70"]180[/TD]
[TD="class: xl70"]263[/TD]
[TD="class: xl72"]98[/TD]
[TD="class: xl65"]923[/TD]
[TD="class: xl65"]138[/TD]
[TD="class: xl65"]470[/TD]
[TD="class: xl65"]516[/TD]
[TD="class: xl65"]460[/TD]
[TD="class: xl65"]518[/TD]
[TD="class: xl65"]266[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl69, colspan: 2"]Criteria for formula:[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, colspan: 12"]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[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, colspan: 5"]Then counts 8 cells from the start of the valid sequence[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl68, colspan: 6"]Formula returns the value in the 8th cell, the result I want returned is italic & underlined[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl68, colspan: 11"]Formula would be in column u which I could drag down and would work regardless of where the sequence starts[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1280"]
<colgroup><col width="64" span="20" style="width:48pt"></colgroup><tbody></tbody>[/TABLE]
 
Sort by date Sort by votes

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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)
 
Upvote 0
graemestown said:
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)
Click to expand...

The blank cell is less than 50, it is not necessary to set the criteria
 
Upvote 0
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
[TABLE="width: 121"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]List[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 409"]
<colgroup><col><col span="6"><col></colgroup><tbody>[TR]
[TD]Answer[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]9[/TD]
[TD]21[/TD]
[TD]34[/TD]
[TD]45[/TD]
[/TR]
[TR]
[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]
</tbody>[/TABLE]

Thanks!
 
Upvote 0
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>
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
B
  • Question Question
average based on time
Replies
1
Views
395
baqer
B
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
268
kitm
K
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
611
Greenbehindthecells
G
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
595
earthworm
earthworm

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top