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
Thanks a lot, its working fine.
I am trying to do same trick with sumifs rather than vlookup, struggling. any help will be greatly appreciated.
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
to elaborate I want sumrange to advance only one time in lookupsheet , while I drag the sumifs formula in parent sheet 6 columns.
 
Upvote 0
You would need to use something on the lines of

=SUMIFS(INDEX(Sheet2!$A:$D,0,INT((COLUMNS($B2:B2)-1)/7+1)),Criteria_Range1,Criteria1.....)

Note the position of the 0 in the middle of the INDEX function, the formula will encounter some strange errors if this missing or misplaced.
 
Upvote 0
thanks, its working, SUMIFS(INDEX('Weekly Actual'!$D:$AM,,INT((COLUMNS($D:D)-1)/7+1)),'Weekly Actual'!$A:$A,$B3,'Weekly Actual'!$C:$C,"AMS BAU")
 
Upvote 0
Hi , Thanks a lot for your support much appreciated. Another one, this time, I want to do the reverse of what I did earlier. That is when I drag right, formula should skip multiple columns referenced. I want to jump multiple columns ( 6 to be precise)

I tired below with different multipler combination, seems this works when we want to jump odd number of columns.
=INDEX(AppView!$E2:$DN2,,COLUMNS($E2:E2)*4-4+1)
 
Upvote 0
something like below col,s I want to pick in from the range in index.
1​
8​
15​
22​
 
Upvote 0
I want to do the reverse of what I did earlier.
I'm not following what you mean, your formula implied that you found what you needed. Did I miss something?

In the method that I suggested, the number in bold is the distance between columns, i.e. 1 more than the number to skip. INT((COLUMNS($B2:B2)-1)/7+1))

Although it's not a method that I've used, yours should do exactly the same, but you need to keep both numbers the same. COLUMNS($E2:E2)*7-7+1

The COLUMNS(range) should always start with a single column and the INDEX range should always start at the first column of data to return.
 
Upvote 0
My apologies for not stating the issue. First issue is resolved. This is new one which I am trying to solve using the trick you shared for first issue. Very similar to first one.

This is the appview sheet. I am trying to find difference of BAU and Project forecasts and actual. After , E to O , same data repeats for next week.

AppView Sheet
1590170007380.png

Here I am taking the difference in another sheet. Col B , C , then repeat for next weeks. I guess issue might be that Col B is not to C but Col D. So in variance sheet also I am skipping one column.
Variance
1590170319521.png
 

Attachments

  • 1590170048413.png
    1590170048413.png
    30.2 KB · Views: 2
Upvote 0
Although it's not a method that I've used, yours should do exactly the same, but you need to keep both numbers the same. COLUMNS($E2:E2)*7-7+1
Can the number 7 be replaced by fraction say 3.5
Following seems to be working..

Rich (BB code):
=INDEX(AppView!$E2:$DN2,,COLUMNS($E2:E2)*3.5-3.5+1) - INDEX(AppView!$I2:$DN2,,COLUMNS($I2:I2)*3.5-3.5+1)
 
Upvote 0
It can work but it can get messy, anything other than an integer (whole number, no decimals / fractions) will create an irregular pattern.

Using 3.5 is going to follow the index pattern of 1,4,8,11,15,18,22 (note that the number of columns skipped alternates between 2 and 3.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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