Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

vlookup excel 2007 to changes as formula is dragged across columns

Hi Expert's

is it possible to write a vlookup formula...so as you drag the formula across from A2:Z2 the column reference changes to the next one along..

so =if(isna(vlookup(d3,d45:j55,3,0)),"",(vlookup(d3:j55,3,0)))
So the 3 changes. ..
0
route217
Asked:
route217
2 Solutions
 
MacroShadowCommented:
That is the default behavior. Insert the formula in one cell, as you drag it vertically the column references will get updated.
0
 
route217Author Commented:
So...default. .meaning. ..no it cannot be done??

And firstly thanks for the feedback Macro Shadow
0
 
MacroShadowCommented:
No, it can be done and in fact it will be done automatically when you drag your formula vertically.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
route217Author Commented:
Sorry. ..I am looking for horizontal across. .
0
 
MacroShadowCommented:
Sorry that's what I meant.
0
 
route217Author Commented:
Apologies. ...the 3 changes to 4 the next column along...
0
 
MacroShadowCommented:
Isn't that what you want?
0
 
route217Author Commented:
Yes I do....I have just written the vlookup and dragged the formula across from d2 to f2 and column reference has stay fixed at 2.....
0
 
MacroShadowCommented:
Please upload a sample.
0
 
route217Author Commented:
Worked it out....use column(b1),false.  And drag
0
 
Rob HensonFinance AnalystCommented:
When you have cell references within a formula they will change as you copy/drag across/down unless you append the row or column reference with $.

However, you are referring to the offset value in the lookup formula which is not a cell reference, it is just a number.

That offset number can be calculated in a number of ways.

COLUMN() - will give the column number of the column in which the formula resides, can be useful if the source data and the lookup based data have the sqame column layout. If the layout is basically the same but slightly different column poisitioning, you can use COLUMN()+n where n is the number of colummns different.

MATCH(value,range,type) - can be used to match a column header. The value would refer to the column header of the destination data, the range would be the row of headers in the source data, type would be 0 to find an exact match but that does mean that the column headers do have to match exactly.

To stop the default column changes, you can amend your formula to:

 =IF(ISNA(VLOOKUP($D3,$D$45:$J$55,3,0)),"",VLOOKUP($D3,$D$45:$J$55,3,0))

As you're using Excel 2007, it can be simplified further to:

=IFERROR(VLOOKUP($D3,$D$45:$J$55,3,0),"")

Thanks
Rob H
0
 
SteveCommented:
OK, the question was about changing the lookup column from 3 to 4 to 5 etc not the lookup range.
The asker seems to have found the solution in the Column() function.

I would tend to use teh following (dropping the isNA):

=IFERROR(vlookup($d3,$d$45:$j$55,column(C1),0),"")
0
 
route217Author Commented:
Excellent. ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now