I need to write a 2 Vlookup formulas at Cell E25 to display the date a loan
will be repaid and secondly the total interest paid.
I have an analysis table called 'table' with 6 columns
1. Date
2. Opening loan balance
3. Add interest
4. Less payment
5. Closing balance
6. Cumulative interest
I can get the sheet to work easily but the vlookups are an entirely
different matter, I just cant get them to work. My boss has specified that he
wants lookups.
TIA Chris

One way:

You can't use VLOOKUP for the date, since the date is to the left of the
balance. You can use INDEX(MATCH()), though:

=INDEX(table, MATCH(0, OFFSET(table,0,4,,1),FALSE),1)

total interest paid is more straightforward:

=VLOOKUP(0,OFFSET(table,0,4,,2),2,FALSE)




In article ,
"Bertiesmum" wrote:

I need to write a 2 Vlookup formulas at Cell E25 to display the date a loan
will be repaid and secondly the total interest paid.
I have an analysis table called 'table' with 6 columns
1. Date
2. Opening loan balance
3. Add interest
4. Less payment
5. Closing balance
6. Cumulative interest
I can get the sheet to work easily but the vlookups are an entirely
different matter, I just cant get them to work. My boss has specified that he
wants lookups.
TIA Chris

Thank you for sending me the links JE, the solution is already in place, I do
appreciate your time. Regards Chris.


"JE McGimpsey" wrote:

One way:

You can't use VLOOKUP for the date, since the date is to the left of the
balance. You can use INDEX(MATCH()), though:

=INDEX(table, MATCH(0, OFFSET(table,0,4,,1),FALSE),1)

total interest paid is more straightforward:

=VLOOKUP(0,OFFSET(table,0,4,,2),2,FALSE)




In article ,
"Bertiesmum" wrote:

I need to write a 2 Vlookup formulas at Cell E25 to display the date a loan
will be repaid and secondly the total interest paid.
I have an analysis table called 'table' with 6 columns
1. Date
2. Opening loan balance
3. Add interest
4. Less payment
5. Closing balance
6. Cumulative interest
I can get the sheet to work easily but the vlookups are an entirely
different matter, I just cant get them to work. My boss has specified that he
wants lookups.
TIA Chris