If and Vlookup with automatic update for yearly forecast

Candyland25

New Member
Joined
Dec 2, 2016
Messages
33
[FONT=&quot]Hello,
Try to write a formula with if and vlookup and I think I got it to work but I'm having trouble making to work for a entire year I need.[/FONT]

[FONT=&quot]Example:
Sheet 1
October November
Names Hours Hours
John Smith 25 15
Katie Wilson 45 55
Jeremy Pate 37 20[/FONT]

[FONT=&quot]Sheet 2
October
Names Hours
John Smith
Katie Wilson
Jeremy Pate[/FONT]

[FONT=&quot]=IF($B$1='Sheet1'!$B$1,VLOOKUP(A1,'Sheet1'!A:K,2,0),"no")[/FONT]
[FONT=&quot]With this formula if B1(October) is in sheet1 than it matches and the formula shows me the hours for month of October.

However for month of November unless I change the formula I can't get the hours to appear in Sheet 2. Sheet 2 has ONLY one column for hours and I can't add anymore due to reporting restrictions. I can change B1 cell from October to November. How do I get excel to automatically update it self if change "B1" Cell from October to November.[/FONT]

[FONT=&quot]Please help. Thank you[/FONT]
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this
November
Names
Hours
John Smith
15
Katie Wilson
55
Jeremy Pate
20

<tbody>
</tbody>
formula: =HLOOKUP($B$1,Sheet1!B:C,MATCH(A3,Sheet1!A:A,0),0)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,371
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top