Solved

vlookup excel 2007 to changes as formula is dragged across columns

Posted on 2014-01-15
13
315 Views
Last Modified: 2014-01-28
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
Comment
Question by:route217
13 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782112
That is the default behavior. Insert the formula in one cell, as you drag it vertically the column references will get updated.
0
 

Author Comment

by:route217
ID: 39782115
So...default. .meaning. ..no it cannot be done??

And firstly thanks for the feedback Macro Shadow
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782119
No, it can be done and in fact it will be done automatically when you drag your formula vertically.
0
 

Author Comment

by:route217
ID: 39782120
Sorry. ..I am looking for horizontal across. .
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782121
Sorry that's what I meant.
0
 

Author Comment

by:route217
ID: 39782125
Apologies. ...the 3 changes to 4 the next column along...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782129
Isn't that what you want?
0
 

Author Comment

by:route217
ID: 39782136
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39782162
Please upload a sample.
0
 

Author Comment

by:route217
ID: 39782167
Worked it out....use column(b1),false.  And drag
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 39782190
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
 
LVL 24

Assisted Solution

by:Steve
Steve earned 250 total points
ID: 39782237
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
 

Author Comment

by:route217
ID: 39782321
Excellent. ...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now