# Last cell populated only

#### petiteshiraz

##### Board Regular
In columns B through M I have information that corresponds to data for each month. When January comes, I enter that month’s data, when February comes, I do the same.

In column N, I want to have only the info from the last cell that is filled in. In other words, if there is data in cells B4, C4, and D4, I want cell N4 to be populated with the data from cell D4.
How do I do this?

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### AD_Taylor

##### Well-known Member
Try this. The formula below should be entered into cell N1, so all formulas are relative to that row number.
It should work for any text and numbers entered into columns B:M as long as all cells are filled. If you for example enter B1, then D1 it will not work. The cells must be filled in sequence.

=IF( COUNTIF(\$B1:\$M1, "?*") + COUNT(\$B1:\$M1) = 0, "No Data!", INDIRECT(ADDRESS(ROW(\$N1), COUNTIF(\$B1:\$M1, "?*") + COUNT(\$B1:\$M1) + COLUMN(\$B1) - 1)))

#### Dryver14

##### Well-known Member
If you are entering numbers in these cells this should do

=LOOKUP(9.99999999999999E+307,B4:M4)

#### petiteshiraz

##### Board Regular
That works!!!!

Thank you so much.

#### Rick Rothstein

##### MrExcel MVP
ADVERTISEMENT
If you are entering numbers in these cells this should do

=LOOKUP(9.99999999999999E+307,B4:M4)
That works!!!!

Thank you so much.
This "simpler" formula will work whether the contents of the cells are text or numbers...

=LOOKUP(2,1/(B4:M4<>""),B4:M4)

#### Jonmo1

##### MrExcel MVP
This "simpler" formula will work whether the contents of the cells are text or numbers...

=LOOKUP(2,1/(B4:M4<>""),B4:M4)

In what way is that "simpler" ?
Shorter...Yes
But I wouldn't say Simpler.

The first will be more efficient, given the data is numeric.

#### Rick Rothstein

##### MrExcel MVP
ADVERTISEMENT
In what way is that "simpler" ?
Shorter...Yes
But I wouldn't say Simpler.

The first will be more efficient, given the data is numeric.
Notice I put quote marks around the word "simpler"... that was supposed to qualify the word as not being exactly like its definition (and yes, shorter might have been a better word to use).

I'm curious on you last statement... Do you know (or anyone else reading this thread), or is it documented somewhere, how much more efficient we are talking here? I mean, is the percentage difference of adding the calculation for the boolean and then dividing it into 1 to the formula marginal or is it significant?

#### Jonmo1

##### MrExcel MVP
Notice I put quote marks around the word "simpler"... that was supposed to qualify the word as not being exactly like its definition (and yes, shorter might have been a better word to use).

I'm curious on you last statement... Do you know (or anyone else reading this thread), or is it documented somewhere, how much more efficient we are talking here? I mean, is the percentage difference of adding the calculation for the boolean and then dividing it into 1 to the formula marginal or is it significant?
Not sure if it's documented, I'm sure there have been discussions about it on the forum though.

But to my mind, the difference is significant.
This does a simple binary search
=LOOKUP(9.99999999999999E+307,B4:M4)
It ends up only looking at about half of the cells.

But this formula
=LOOKUP(2,1/(B4:M4<>""),B4:M4)
Must eveluate every cell in the range for <>"", AND perform a devision function on every cell in the range, Then do the same basic binary search.

That is a great formula, for mixed values of numbers and text.
But if it's known that the data is all numeric then the first is much better.

Also, if it's known that the data is all text, then a similar can be done using Rept("z",255) for the lookup value instead of BigNum.

#### Rick Rothstein

##### MrExcel MVP
Not sure if it's documented, I'm sure there have been discussions about it on the forum though.

But to my mind, the difference is significant.
This does a simple binary search
=LOOKUP(9.99999999999999E+307,B4:M4)
It ends up only looking at about half of the cells.

But this formula
=LOOKUP(2,1/(B4:M4<>""),B4:M4)
Must eveluate every cell in the range for <>"", AND perform a devision function on every cell in the range, Then do the same basic binary search.
I must take some time and read up on the background to some of Excel's functions... I did not realize LOOKUP did a binary search for its "straightforward" lookups. Yeah, given that, I would agree that the difference is significant rather than marginal. Thanks for following up with me on this.

#### Jonmo1

##### MrExcel MVP
I must take some time and read up on the background to some of Excel's functions... I did not realize LOOKUP did a binary search for its "straightforward" lookups. Yeah, given that, I would agree that the difference is significant rather than marginal. Thanks for following up with me on this.

No problem, that's why the data must be sorted ascending (for standard lookups), Same for VLOOKUP with TRUE, and MATCH with -1 or 1.

Replies
1
Views
194
Replies
30
Views
504
Replies
12
Views
386
Replies
6
Views
233
Replies
5
Views
161

Threads
1,195,951
Messages
6,012,495
Members
441,702
Latest member
chacham

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

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