Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Duplicating formulas in Excel

Posted on 2011-03-04
5
230 Views
Last Modified: 2012-05-11
Hello experts,

In an excel cell, I have:

=VLOOKUP(A2;A85:C331;2;FALSE)

In the below cell, it should be:

=VLOOKUP(A3;A85:C331;2;FALSE)
=VLOOKUP(A4;A85:C331;2;FALSE)
=VLOOKUP(A5;A85:C331;2;FALSE)
.
.
.
It should increase the first parameter, with keeping second and third parameters without any change.

How to do so?
0
Comment
Question by:Muhajreen
  • 4
5 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 35039739
Change your formula to this:

=VLOOKUP(A2;A$85:C$331;2;FALSE)

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35039758
This way you can copy down the formula and it will "fix" the row number on the second and third parameters while incrementing the first.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35039760
Thanks for the grade! :)

regards,

jppinto
0
 
LVL 26

Expert Comment

by:pony10us
ID: 35039792
@jppinto:

Am I missing something or shouldn't it be

=VLOOKUP(A2,$A$85;$C$331;2;FALSE)

Just for readability and if you moved the column left or right without changing the source column from A then it would still point to A.

Just asking.

:)

Pony
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35039821
Yes, if you plan to move the formula from the actual column to another column, you should fix also the column reference but the author didn't mentioned he wanted to copy the formula to other columns, only to other rows. That's why I provided the solution needed for the case that was presented.

Thanks anyway for you post.

jppinto
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 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