Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

index match

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Index / match / greater, equal to

Index / match / greater, equal to

ResolvedVersion 2003

Paul has attended:
Excel Advanced course

Index / match / greater, equal to

I have two sets of data:
One contains a loan principal amount which changes on a specific day once each week. The principal will remain unchanged on this same balance for seven days.
The other contains the principal amount but has daily interest calculated.
I need to keep the two data sheets separate, however, I would like to automatically populate the daily interest sheet with the correct loan principal amount extracted from the weekly sheet.


Thank you

RE: Index / match / greater, equal to

Hi Paul,

Thank you for the forum question.

I am not sure I understand exactly what you want. If you want data populated from a worksheet you just need a link, but I am sure that this is not what you want.

I have attached a workbook where I have calculated the interest and principal weekly and daily.

If it is possible I would like to see the workbook to understand your needs.

You can forward it to:

info@stl-training.co.uk



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

principal.xlsx

RE: Index / match / greater, equal to

Hi,

Thanks for the reponse.
I couldn't see your attachment, nor where I ought to attach mine. However, to clarify, I am trying to automatically populate the principal loan amount from the weekly sheet into the daily sheet. The lookup will need to be adjusted (eg via index / match) since the weekly dates will not match the daily dates. A simple version is included below: Note the principal changes weekly (eg 8-Apr).

Weekly:
Date Principal
1-Aug 5,000
8-Aug 6,000
15-Aug 5,500
Daily
1-Aug 5,000
2-Aug 5,000
3-Aug 5,000
4-Aug 5,000
5-Aug 5,000
6-Aug 5,000
7-Aug 5,000
8-Aug 6,000

RE: Index / match / greater, equal to

Hi Paul,

Thank you for the information.

You only need the Vlookup to do what you want.

If you have the dates starting from A1 on both sheets and the principal starting from B1 on both sheets and the sheet name for the weekly sheet is WEEKLY the Vlookup should look like this in B1 on the Daily sheet:

=Vlookup(a1,WEEKLY!A1:B3,2,true)

By using true the function will look for a perfect match or nearest lowest value and this is exactly what you want.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Excel tip:

Moving between Worksheets without using the mouse

Use the 'Ctrl+PgDn' and 'Ctrl+PgUp' keys.

'Ctrl+PgDn' will move to the right and 'Ctrl+PgUp' will move to the left one worksheet at a time.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.