# INDEX COUNT FORMULAS

#### DrBourse

##### New Member

Don’t really like to bother you, I only do so after I’ve tried several times to get a formula to work…

Not having much luck with my current problem..

Before I give up altogether I thought I would “Ask for help from an Expert”..

In my “spreadsheet A”, I have a formula in a cell in column G =Index(B:B,Count(B:B)) that picks up the last figure in column B, that has been working well for years..

Now I’m trying to get that same “last Figure” from spreadsheet A into my new “spreadsheet D”.

I’ve tried formulas like =Index(B:B,Count(B:B)) & various =(IF & =IF(AND combinations, so far all I get is the standard Error window, with varying messages about Broken Formulas etc

If anyone has the time to spare I REALLY would appreciate some help.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Alex Blakenburg

##### Active Member
I am surprised the index function consistently worked for you in the past, it only works in quite specific scenarios.
If you do this is a much more robust option.
Excel Formula:
``=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)``
xlookup(look for True, in the B:B<>"", return value from B:B, if not found return "", 0=exact match, -1 = start looking from the bottom up)

#### DrBourse

##### New Member
I am surprised the index function consistently worked for you in the past, it only works in quite specific scenarios.
If you do this is a much more robust option.
Excel Formula:
``=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)``
xlookup(look for True, in the B:B<>"", return value from B:B, if not found return "", 0=exact match, -1 = start looking from the bottom up)
Hi Alex,
Thanks for that info - I will try that later tonight.
My main problem is linking the 2 spreadsheets with the Index(Count formula - the following formula construction is missing something

Never used the XLOOKUP before, have used VLOOKUP, & HLOOKUP

Will let you know tomorrow.

Cheers.
Paul K
(DrBourse is an Share Trading alias)

#### Alex Blakenburg

##### Active Member
Hi Alex,
Thanks for that info - I will try that later tonight.
My main problem is linking the 2 spreadsheets with the Index(Count formula - the following formula construction is missing something

Never used the XLOOKUP before, have used VLOOKUP, & HLOOKUP

Will let you know tomorrow.

Cheers.
Paul K
(DrBourse is an Share Trading alias)
I had a play with your index function but it was too specific for my liking. eg
• Count only counts numbers (which includes dates)
• The data has to start from Row 1 to get an accurate last cell
• If it has a heading row which is normally non-numeric you need to add +1
(using CountA may overcome this)
• Any alpha or blank cells in the data (non-numerics) will throw out the count.

#### DrBourse

##### New Member
I am surprised the index function consistently worked for you in the past, it only works in quite specific scenarios.
If you do this is a much more robust option.
Excel Formula:
``=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)``
xlookup(look for True, in the B:B<>"", return value from B:B, if not found return "", 0=exact match, -1 = start looking from the bottom up)
Hi Alex..
Took a while(3 attempts) but the XLOOKUP works well - Huge Thank You M8.
Cheers.

Replies
2
Views
170
Replies
0
Views
98
Replies
3
Views
73
Replies
3
Views
190
Replies
9
Views
549

1,128,022
Messages
5,628,186
Members
416,299
Latest member
arunvistas

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