Forum Discussion

vonryan's avatar
vonryan
Brass Contributor
Apr 26, 2019
Solved

Vlookup

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 con...
excel
  • Twifoo's avatar
    Twifoo
    Apr 26, 2019
    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.
vonryan's avatar
vonryan
Brass Contributor
Apr 26, 2019

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's avatar
    Twifoo
    Silver Contributor
    Apr 26, 2019
    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.
    • vonryan's avatar
      vonryan
      Brass Contributor
      Apr 26, 2019

      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

       

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Apr 26, 2019
        You’re welcome!
    • vonryan's avatar
      vonryan
      Brass Contributor
      Apr 26, 2019

      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

       

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Apr 26, 2019
        I inadvertently forgot to mention earlier. You just need to copy the formula in D38, down the rows.

Resources