Lookup Latest number

**Abrielle** · 07-07-2010, 10:59 AM

I have a workbook for my office bank account. It contains three sheets.

I would like to show the current balance on Sheet 1, i.e. the last item in column H, to another workbook called Payments (which is in a different folder).

I was given a formula =LOOKUP(1E100, Bank balance 2010-2011 $Sheet1$!H:H)

I really don't understand this, particularly what the 1E100 means. Can anyone help please?

**NBVC** · 07-07-2010, 11:05 AM

Generally:

=Lookup(1E100,A:A)

looks for that number which is really 1E+100 i.e. 1 with 100 0's following it.

Lookup looks for the last number in a range that is smaller than or equal to the lookup value.... so it looks for that big number and obvioulsly does not find it... so it gets the last number it comes across....

the syntax to get to the other workbook and sheet is wrong in your formula

=LOOKUP(1E100, 'C:\path\[Bank balance 2010-2011.xls]Sheet1'!H:H)

Note: Most Excellers use 9.999999999e+307 which is considered the biggest number excel recognizes... on the offchance 1E+100 could be a number in your range....

is more closely the syntax.

If you have both books opened, then after you type =LOOKUP(1E+100, go to the other book and select column H and hit enter. It will formulate it for you. When you close the other workbook, it will expand the path....

**Abrielle** · 07-07-2010, 12:07 PM

Thank you so much; that was a great help,

Is there anywhere I can find what some of these symbols mean in the functions:

$ ! ' and perhaps the different types of brackets ( ) [ ]

Maybe I could get to understand a bit more if I could find out.

**NBVC** · 07-07-2010, 12:13 PM

Those are all basic symbols in excel formula writing and referencing.

You would need to do some searches on Beginner Excel tutorials or books.

An example tutorial: http://www.free-training-tutorial.com/formulas.html

**Abrielle** · 04-04-2012, 02:58 PM

I started this thread almost 2 years ago and the solution was very useful during years 2010-11 and (adapted) for 2011-12.

However, I cannot get it to work for year 2012-2013. I cannot see where the formula is wrong so perhaps someone can help me. I am still using Excel 2003.

2011-2012 formula:

=LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2011-2012\Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F)

2012-2013 formula:

=LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2012-2013\Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F

All the folders and workbooks are named the same, with the exception of the years (i.e. 2011-2012 became 2012-2013).

When I use this formula, it just returns zero in the cell in my Payments workbook where I need the current bank balance.