Lookup (1e+100,) for sheet with reverse totals

[hkbs99]

I often use the Lookup (1e+100, ...) function to bring totals from another sheet.

I have just hit a problem with my bank statement. This shows the most recent balance at the top of the sheet so the Lookup (1e+100, BANK STATEMENT!E:E) will not work - it shows the first balance, i.e. the figure at, for example, 1 January at the bottom of the sheet, rather than today's balance at the top of the sheet.

Is there a lookup formula which could bring up the correct figure please?

[Kaper]

If it's always on the top, isn't it in constant location like
=B2
or so?
Probabaly not, because you wouldn't ask
So if not - see yellow banner at the top

[hkbs99]

Thanks for your reply. I'm attaching a sample workbook which, I hope, shows you what I'm trying to achieve.

[TMS]

Something like:

Please Login or Register  to view this content.

... adjust columns as required.

[hkbs99]

Sorry, you've lost me! Does the "*" represent the BANK BALANCE?
Where should the formula be inserted - it doesn't work if I put on Sheet 1.

[TMS]

No, it represents an asterisk ... used as a wildcard. Probably easiest if you just try it in your workbook/worksheet.

[hkbs99]

No, it represents an asterisk ... used as a wildcard. Probably easiest if you just try it in your workbook/worksheet.

I pasted it it but it didn’t work. How would it return the correct answer in Sheet 1 without reference to the cell in Bank Balance?

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[TMS]

I guess you could add the sheet references ...

I was just demonstrating the approach. Thought you might be able to adapt it?

Please Login or Register  to view this content.

Edit: sadly, this doesn't work

[Kaper]

As the most recent position is on top, this shall work:

Please Login or Register  to view this content.

(also if you have some entries for the same day - the top one will be selected)

Restriction: in column A you shall not have any more recent dates, nor numbers greater than ca.43900

[TMS]

Sadly, my formula doesn't work. Kaper's definitely does work.

[hkbs99]

As the most recent position is on top, this shall work:

Please Login or Register  to view this content.

(also if you have some entries for the same day - the top one will be selected)

Restriction: in column A you shall not have any more recent dates, nor numbers greater than ca.43900

Many thanks. That works. I would never have worked it out for myself!