# Formula to skip blank cells

#### FJMD1003

##### New Member
Hi all,

I'm looking to create a formula that will skip past any blanks until it finds the latest and most up-to-date value.

Some context: I wish to return a latest estimate value to a cell (A5), and this value is updated quarterly. Let's say that the quater 1 value sits in cell A1, Q2 in A2, Q3 in A3 and Q4 in A4. I wish cell A5 to display the latest estimate as soon as a user updates it on a quarterly basis, but default to the previous quarter's estimate if that latest view is unavailable.

I've tried using some IF(ISBLANK...) combinations but am getting nowhere - any ideas greatly appreciated!

NB want to try and avoid Macros across this worksheet so a formula solution would be best.

Many thanks for your help guys,

Fred

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

Try eg:

=LOOKUP(9.99999999999999E+307,A2:A100)

#### FJMD1003

##### New Member
Thanks for that tip Andrew though I'm still struggling to make it work in this array e.g.

<TABLE style="WIDTH: 661pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=879><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 374pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl63 height=20 width=497 colSpan=6>Quarterly Revenue results and Fully Year Latest Revenue Estimates (LRE) 2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=82></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=152></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>Q1</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65>Q2</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65>Q3</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65>Q4</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 rowSpan=2 width=152>Full Year Latest Revenue Estimate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68 height=20>Q1 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Q2 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Q3 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Q4 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67 height=20> £ 45.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70> £ 200.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67> £ 90.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70> £ 200.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67> £ 145.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70> £ 195.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67> £ 180.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl69> £ 200.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #7f7f7f; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71>??????</TD></TR></TBODY></TABLE>

I would like it to return a latest estimate value in the cell with question marks in it which automatically updates when the next quarter's data is input as the year progresses.

Thanks for your support with this!

#### Andrew Poulsom

##### MrExcel MVP
Try:

=LOOKUP(2,1/((A2:H2=B2)*(A3:H3)),A3:H3)

#### FJMD1003

##### New Member
Thanks Andrew that worked brilliantly!

Much appreciated,

Fred

#### shuskey06

##### New Member
Try:

=LOOKUP(2,1/((A2:H2=B2)*(A3:H3)),A3:H3)

I'm looking to the same thing but in a spreadsheet calendar.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,618
Messages
5,838,390
Members
430,545
Latest member
c3310

### 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.

### Which adblocker are you using?

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

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