Vlookup Help

tomehta

New Member
Joined
May 20, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hello
In attached excel screen shots, in Col I (sheet 1), I want Col C value (from sheet 2), similarly in Col P (sheet 1) want Col D value( from Sheet 2). I am trying with variations of Columns function but not able to crack. I want to create formula in col B sheet 1 and drag it. I dont want to manually update formula in Col I and Col P ( and hence forth). Any help will be greatly appreciated.
 

Attachments

  • Sheet 1.png
    Sheet 1.png
    34 KB · Views: 14
  • Sheet 2.png
    Sheet 2.png
    18 KB · Views: 14
  • dragged formula in sheet 1 cols.png
    dragged formula in sheet 1 cols.png
    32.5 KB · Views: 13

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Thanks for your prompt response, I changed as suggested but seems not working. I might me missing something
 

Attachments

  • Col-B.png
    Col-B.png
    35.3 KB · Views: 9
  • Col-I.png
    Col-I.png
    32.9 KB · Views: 10
Upvote 0
works like a charm.. tho hav'nt got hang of what is going on ..:) any background/doc link to that will be much appreciated.
 
Upvote 0
It's adding 1 to the number in the yellow cell. Wk1 is in the 2nd column Wk2 in the 3rd, etc.

Might be better to use RIGHT(B$1,2)+1 ready for Wk 10 onwards.
 
Upvote 0
well I understood how its working. We are using the col names. In case , I dont want to manipulate column names, how it can be achieved ?
 
Upvote 0
If you will always have 6 other columns between the formulas, then you could use

INT((COLUMNS($B2:B2)-1)/7+1)

If the number of columns varies then you will not be able to do it without using the column headers.
 
Upvote 0
Earlier screen shot were from toy example, My actual column names are as below, so bit hard to manipulate using col names.
 

Attachments

  • actual col names.png
    actual col names.png
    6.8 KB · Views: 6
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top