SOLVED

Vlookup

Brass Contributor

Dear All,

 

I would like some help with Vlookup

 

What I need to do is look at 2 seperate list of dates. If the dates are the same from each worksheet then I need to return to a specific cell contents of another cell.

 

Meaning the following.

 

i look at (B38:B84) on one sheet, I look at 'Incoming Finance'!B5:B348) on another sheet if the contents (dates) are the same then I want to copy the contents of incoming finance cell E5:E348 to (D38:D84)

 

In english this means if the two dates are the same copy the values from a horizontal offset cell to another cell in a range of cells.

 

More detailed is if worksheet "Income Tracking" cell B38 Date is the same as any cell within the range E5:E348 on worksheet Incoming Finance. then copy the contents of the cell (HXXX) from income tracking" worksheet to cell FXXX on "Income tracking" worksheet where cell FXXX is the same date as the the two compared dates.

 

Best Regards

 

Vonryan

 

17 Replies
I suggest that you attach your sample file so that I can visualize your desired results.

@Twifoo 

 

Hi, please find attached the actual file. Please treat with confidence.

 

Best Regards

 

Ian

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,

 

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

 

@vonryan 

@Twifoo 

 

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.

 

 

best response confirmed by vonryan (Brass Contributor)
Solution
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.

 

Now how do I get the Vlookup to do it for all Saturdays in column B of the Income Tracking Schedule (US$) work sheet.

 

I appreciate all your help so far.

 

Thank you

 

Vonryan

 

I inadvertently forgot to mention earlier. You just need to copy the formula in D38, down the rows.

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

 

You’re welcome!

Hi there,

 

If you are interested, maybe you can help me with another issue as I am a real beginner with Excel if you want.

 

On the Income Tracking Schedule (US$) I have added a deviation (+/-) column in which I calculate the deviation of what has been remitted in foreign currency and what should have been remitted. The formula is =D38-C38, very simple, but my issue is I want to automatically do this for each Saturday instead of manually dragging the formula down in the column every Saturday to update the spreadsheet.

 

What I would like to do is check if the date in column B is in the past, if it is then apply the formula to the cell of that date which will calculate the deviation otherwise leave the cell blank.

 

I cannot find a funtion that will do this.

 

I have attached the revised spreadsheet.

 

Looking forward to your assistance.

 

Vonryan

 

@Twifoo 

In that case, you only need as simple IF in F38, copied down rows, like this:
=IF(B38<=TODAY(),
D38-C38,
"")

Hi there@Twifoo 

 

Once again, perfect just what I needed.

 

Thank you so much. Saved me hours trying to figure it out.

 

Best Regards

 

Vonryan

 

Welcome again.

Hi @Twifoo,

 

Are you up for a new challenge. I am coming direct to you as you have been a great help so far. Your futher assistance would be greatfully appreciated.

 

I am venturing into VBA as it appears that this is the only way I can achieve what I want.

 

Ok here goes.

 

On the Incoming Finance work sheet there are a lot of rows that have very little data in them. This is OK as all entries on this sheet are manual. What i want to do is hide rows if the date is in the past and the column E is empty for that date.

 

I tried creating the following in VBA

 

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Hide": Rows("5:10").EntireRow.Hidden = True
Case Is = "Unhide": Rows("5:10").EntireRow.Hidden = False
End Select
End If
End Sub

 

This works when I type Hide or Unhide in cell A1.

 

I want to modify this to say the following:

 

If Cell (B5) <= TODAY() and Cell (E5) <>0 then Row("5").EntireRow.Hidden = True

 

But I want to do this for all rows in the worksheet.

 

Are you willing to take on this challenge.

 

Best Regards

 

Vonryan

I’m sorry. VBA is not my forte.

Hi @Twifoo 

 

Thanks for getting back to me. Again I am grateful for all of your help so far. I will use the community to see if anyone can help.

 

Regards

 

Vonryan

 

 

1 best response

Accepted Solutions
best response confirmed by vonryan (Brass Contributor)
Solution
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.

View solution in original post