Vlookup Help

tomehta

New Member
Joined
May 20, 2020
Messages
25
Office Version
2016
Platform
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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,542
Office Version
2019
Platform
Windows
Try changing @COLUMNS(...) to Right(B$1)+1
 

tomehta

New Member
Joined
May 20, 2020
Messages
25
Office Version
2016
Platform
Windows
Thanks for your prompt response, I changed as suggested but seems not working. I might me missing something
 

Attachments

tomehta

New Member
Joined
May 20, 2020
Messages
25
Office Version
2016
Platform
Windows
works like a charm.. tho hav'nt got hang of what is going on ..:) any background/doc link to that will be much appreciated.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,542
Office Version
2019
Platform
Windows
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.
 

tomehta

New Member
Joined
May 20, 2020
Messages
25
Office Version
2016
Platform
Windows
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 ?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,542
Office Version
2019
Platform
Windows
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.
 

tomehta

New Member
Joined
May 20, 2020
Messages
25
Office Version
2016
Platform
Windows
Earlier screen shot were from toy example, My actual column names are as below, so bit hard to manipulate using col names.
 

Attachments

Watch MrExcel Video

Forum statistics

Threads
1,099,038
Messages
5,466,170
Members
406,471
Latest member
tsou88

This Week's Hot Topics

Top