Forum Discussion
Vlookup
- A simple VLOOKUP like this in D38 is all you need:
=VLOOKUP(B38,
'Incoming Finance'!B$5:M$348,
5,0)
Note that the col_index_num argument of 5 is hard-coded because there is no identical column label in the Incoming Finance Sheet against which the corresponding column label in the Income Tracking Schedule Sheet may be matched.
hI,
For a further explanation in the file you will see Incoming Finance worksheet with dates in column B.
You will also see on worksheet Income Tracking Schedule (US$) dates in column B. these are the Saturdays of each week of the project.
So for every Saturday on the Income Tracking Schedule (US$) i would like to retrieve the Running Total (US$): value in Column F on the incoming Finance worksheet for tat Saturday and place it in the Income Tracking Schedule (US$) column D for that particular Saturday.
Hope you can still help.
Regards
Ian
- A simple VLOOKUP like this in D38 is all you need:
=VLOOKUP(B38,
'Incoming Finance'!B$5:M$348,
5,0)
Note that the col_index_num argument of 5 is hard-coded because there is no identical column label in the Incoming Finance Sheet against which the corresponding column label in the Income Tracking Schedule Sheet may be matched.Hi Twifoo
That seems to have worked for cell B38, and it does return the correct value.
So I modified your Vlookup statement to =VLOOKUP(B38:B84,'Incoming Finance'!B$5:M$348,5,0) and all the cells now show the current value.
I tried this formula before and did not get the correct answer. You have shown me that the hard coded value of 5 in the formula includes the original cell as the first cell, i.e 1 Column B of Incoming Finance.
I used 4 as I did not count the original column (B) as part of the offset.
You have been a great help and I thank you.
Best Regards
Vonryan
Thank you
Vonryan
- In the Income Tracking Schedule Sheets, your labels for Columns C to E are Planned Remittance Total, Actual Weekly Running Total, and Weekly Remittance Required. How do you calculate the amounts to be returned under each Column for each date in Column B? No identical labels exist in the Incoming Finance Sheet. Please manually enter your desired results under each Column and explain how you calculated each of them so that I can decipher the formulas applicable thereto.
Hi and thanks for getting back to me.
In answer to your questions please find below
your labels for Columns C to E are Planned Remittance Total, Actual Weekly Running Total, and Weekly Remittance Required.
How do you calculate the amounts to be returned under each Column for each date in Column B?
These are calculated figures based on the following:
Planned Remittance is based on =ROUNDUP(C2/C7,1) which is budget / No of weeks remaining
Actual Weekly Running Total is based on ='Incoming Finance'!E7. This a manual added value that I am trying to automate with the Vlookup
Weekly Remittance Required is based on =C38 which is derived from a =ROUNDUP(C2/C7,1)
What I want to achieve is a formula via Vlookup or some other method that will look at the dates on both worksheets Incoming Finance and Income Tracking Schedule (US$) and where the dates match, ie Saturday 30th March 2019 take the value that is given in Running Total (US$): column F of Incoming Finance and place it in column D of the Income Tracking Schedule (US$) worksheet at exactly the same date. In this example this would be cell D38.
I am requesting a method of doing this for all cells in column D for the Income Tracking Schedule (US$) worksheet.
I hope this clarifies things for you, if not then please ask for more information.
I appreciate all your time and effort thus far.
Regards
Vonryan
No identical labels exist in the Incoming Finance Sheet. Please manually enter your desired results under each Column and explain how you calculated each of them so that I can decipher the formulas applicable thereto.