Free Microsoft Excel 2013 Quick Reference

Copy VLOOKUP Formula

I need to copy a vlookup formula down a specific column. However, the
lookup_value needs to change from row to row when I copy the formula since it
references the contents of the cells in column A. Also the contents of the
cells in column A are text not numbers therefore the INDIRECT function is not
working for me.

Example
A B
1 ROAA010A VLOOKUP("ROAA010A",'[Allocation Data.xls]Sheet1...
2 ROAB070A VLOOKUP("ROAB070A",'[Allocation Data.xls]Sheet1...
3 ROAM050A VLOOKUP("ROAM050A",'[Allocation Data.xls]Sheet1...
.. . .
.. . .
.. . .

Any help will be greatly appreciated.

Thanks in advance


Post your answer or comment

comments powered by Disqus
Hello guys,

I did some research first but did not find what i was looking for.
my problem is the following;

when given in data in a cell ,
there should be looked in a different sheet to find the matching data and count the number of times a certain data that comes back every 3 cells are filled in .
With the number of counts you have , you should copy the vlookup formula that number of times in the first worksheet

It may sound bizar but perhaps everything will be more clear with the file .

Can anyone help?

I have spread sheet with food items listed. there are subcategories [i.e. bread items] followed by a list of items. then another category, and so on. i have a vlookup formula in the "non category merged cells' "BAKERY" is merged to create headerlike divisions. is there a way to keep the headers and copy the vlookup formula "through" the headers and leave the headers the way they are? i'm not very optimistic but you guys rule! Thanks for any help.
Paper123456dfssdfsdf456145sdfsddfsdf56465sdfdssdfsdfBakery34566/2 LB sdfsdf566/2 LB sdfsdf4566/30 OZ dsf

Morning

I have a filtered column which shows all blank cells.

Within D3 i have a vlookup formula, how can i copy this formula
into the remaining blank cells ?

So the result when i take off the filter will report the original
date plus my copied vlookup results ?

I am using both 2003 & 2007 for this operation !
Many Thanks

Russ

I need to copy a vlookup formula down a specific column. However, the
lookup_value needs to change from row to row when I copy the formula since it
references the contents of the cells in column A. Also the contents of the
cells in column A are text not numbers therefore the INDIRECT function is not
working for me.

Example
A B
1 ROAA010A VLOOKUP("ROAA010A",'[Allocation Data.xls]Sheet1...
2 ROAB070A VLOOKUP("ROAB070A",'[Allocation Data.xls]Sheet1...
3 ROAM050A VLOOKUP("ROAM050A",'[Allocation Data.xls]Sheet1...
.. . .
.. . .
.. . .

Any help will be greatly appreciated.

Thanks in advance

I am using vlookup, and I have about 20 columns of stats I want to look up. For the first stat, I am using the vlookup in column AJ, to look up stats in column C. This is the code I am using. =IF(LEN(AH2)>1,VLOOKUP(AH2, $A$2:$AC$31, 3, FALSE),VLOOKUP(AG2,$A$2:$AC$31,3,FALSE)) Now, I could alter this code manually when I put it in in the adjacent columns after AJ, like AK, AL, AM, etc, to look up the stats in Column D, E, F, etc, but I would rather just copy it horizontally to the right.

Basically, here is the problem. When I copy the code a column to the right, almost everything in the formula changes to a column to the right, which is a problem.

This is the code after it gets copied.

=IF(LEN(AI2)>1,VLOOKUP(AI2, $A$2:$AC$31, [i]3[/], FALSE),VLOOKUP([B]AH2,$A$2:$AC$31,[i]3[/],FALSE)) I put in bold everything that changed when I copied it a column over, but that shouldn't change. And in italic, the number 3 specifying which column to look up data from, didn't change, when it's the part that needs to change.

Basically Im wondering how i can automatically copy the formula into 20 more columns, to look up my other 20 stats, and have the number 3 change for each other column (so 4, 5, 6, 7, etc), without the stuff in bold changing. How do I do this?

How do I copy a VLOOKUP formula from multiple sequential worksheets to a single sheet?

I am creating an excel file to keep track of baseball statistics. I have one worksheet for each game.

Each game has a box score and player stats for the entire game. Each player has been assigned a number (A1, A2, etc).

Each player also has their own season/career statistic page on their own worksheet. Because each player can bat in a different spot in the lineup, Iíve used a VLOOKUP function to find each playerís row of statistics for each game to carry over to their player page.

Hereís an example of the formula Iíve used:

=VLOOKUP("A1", 'G1'!$B$10:$AI$24, 4, 0) (A1 is player 1, G1 is the worksheet for Game #1, B10:AI24 is the range of stats for the player for every game). When I copy the formula down for all the following games (G2, G3, etc) the G1 does not change. Is there a way to do this?

If I type in 3 columns manually and try to drag and copy, it just copies G1, G2, G3, G1, G2, G3, G1, G2, G3.

Any help with this would be GREATLY appreciated. I hope Iíve explained myself well enough to be understood. Iím learning Excel as I go, so Iím certainly far from proficient with it.

I was given this as a suggestion, but it comes back with an error:

from your given formula above try this one...

=VLOOKUP("A1",INDIRECT(""&G1&"'!$B$10:$AI$24"), 4,0)

Thank you for your help,
Damian

How do I copy a VLOOKUP formula from multiple sequential worksheets to a single sheet?

I am creating an excel file to keep track of baseball statistics. I have one worksheet for each game.

Each game has a box score and player stats for the entire game. Each player has been assigned a number (A1, A2, etc).

Each player also has their own season/career statistic page on their own worksheet. Because each player can bat in a different spot in the lineup, Iíve used a VLOOKUP function to find each playerís row of statistics for each game to carry over to their player page.

Hereís an example of the formula Iíve used:

=VLOOKUP("A1", 'G1'!$B$10:$AI$24, 4, 0) (A1 is player 1, G1 is the worksheet for Game #1, B10:AI24 is the range of stats for the player for every game).

Here is where I get stuck................When I copy the formula down for all the following games (G2, G3, etc) the G1 does not change. Is there a way to do this?

If I type in 3 columns manually and then try to drag and copy, it just copies G1, G2, G3, G1, G2, G3, G1, G2, G3.

Any help with this would be GREATLY appreciated. I hope Iíve explained myself well enough to be understood. Iím learning Excel as I go, so Iím certainly far from proficient with it.

The workbook I've attached with this is just a sample of the entire thing. I have hundreds of pages, but I've just included a single player page and 5 game pages. Hopefully this is enough for someone to understand what I'm trying to accomplish.

Thanks,
Damian

I have a very large spreadsheet with over 400 columns of data, I wish to copy a Vlookup formula to each alternate column, this is so I can validate data between two different spreadsheets.
The number of rows in the spreadsheets differ, but I also need to be able to copy and paste the formula into each row of each alternate column as well. I have been looking at various methods to do this but cannot find one that incorporates all the functions necessary to complete these tasks.
I am looking for a macro which could be edited to achieve this goal fr each spreadsheet. or one that can identify the total number of rows present, this could look in Column A, and for the last cell in row 1, which contains my column header data + 1 being the last column I would need to paste the formula.
Also, when pasting the formula to the alternate columns, I need to amend the Col_Index_Num in the Vlookup formula to increment by one for each entry, so it refers to the correct column in the lookup spreadsheet.

The one saving grace is that there are only between 90 and 500 rows of data in the final spreadsheets, the width is the main problem, I do not fancy having to insert all these by hand.
Any help would be appreciated.

This sounds so simple, I am embarrassed I'm so stumped.
I am trying to get a discount group listed in each row that corresponds with a classification group number.
The spreadsheet contains 25,000 items.
Column A contains unique identifiersColumn H contains classification groupH2:H100=110
H101:H156=149
H157:H205=201
Worksheet sorted by Column H ascending order. Column H is right most column.

Vlookup array on separate worksheet within same workbook and is right most on sheet. Have tried sorting in ascending order by both columns.
Column N = Classification groupColumn 0 = Discount GroupN2=110 - O2=1
N3=149 - O3=2
N4=201 - O4=3

All columns formatted as numbers.

Formula =VLOOKUP(H2,!vlookup$N$2:$O$173,2)

This returns the correct value in the cell that it is entered in originally, but when I copy it down the column, it returns "1" all the way down the column! It doesn't seem to copy the formula, even if I "paste special", formula. If I enter the same formula down in another cell in column H, it returns the correct value, but not if I copy. What am I doing wrong?
I so appreciate any help.

Good Day,

I have got a problem with copying a vlookup formula.

I have got two lists of numbers in a sheet. with vlookup I can put data behind the first colum. The prblem is that when I copy the Formula #N/A shows up. Please Help me with this.

I am trying to copy a vlookup formula to a whole column of cells with out the
formula changing the "table_array" part of the formula. When the formula is
copyed excell automaticaly changes the "lookup_value" to match the row number
that the cell is copied to. I want it to continue to do that without changing
the "table_array" part of the formula - Thanks in advance

When working with an Excel file, I have inserted a VLOOKUP command which works fine; however, when I copy the command and paste it, using Paste Special - Formula, in the additional cells of the column, I am getting some discrepancies with regard to the formula and its function, primarily in the Table Array entry. The numbers change in Table Array as the column progresses down. I've used simply Copy and Paste and still get the same issue. Is there a way I can copy and paste this VLOOKUP formula and get a consistent formula all the way down the column? Thanks. WDP

Hi everyone,
Could you help me convert this short VLOOKUP formula into a Macro?
Appreciate your help. Thank you.

Formula:
=VLOOKUP(AA6,AA8:AB10,2)

Please note that I do not want to just copy and paste this formula on the cell.
I am trying to avoid to have a formula on the cell because whenever I copy them, I get the NA# rather than the value.
Tried in vain seeking help in other forums but with no luck.
I hope someone here could find time to look at this.
Thank you in advance.

When I copy my vlookup formula my range changes. I want it to stay unchanged, but to change the intial reference cell.
I cant copy and drag because Excel changes the range. I tried the F2 copy enter move to cell paste, but the commas in my formula make like a delimited paste and separate into columns and cells instead of pasting the formula.
This is my formula:
=VLOOKUP(E21,A3:B14,2,FALSE)
I want the next one to be =vlookup(f21,A3:B14,2,false)
then the one after that =vlookup(g21,A3:B14,2,false)
ect... (I want to do this out about 200 rows)
Thanks in advance for the help!
I am new to the site & Vlookup- (Thanks Dave!)

Hello all,
I have two workbooks: StaffDB and StaffDoB

I have staff database in StaffDB that contrain information about all employees in the first workbook. The second workbook has only two columns: StaffID and DoB.

I used the VLOOKUP formula in StaffDoB workbook to help me enter the employee ID in the first column, to return the DoB of that employee in the second column.

My issue is with the results displayed. The date of birth will show, but in the formula bar the VLOOKUP formula is also displayed. Now I don't want to hide the formula bar here, but I want to have the dates displayed to be copied as value so that when it looks up the date from the first workbook StaffDB, it will copy it as date. So that if I had to delete StaffDB, the value in StaffDoB should remain there as well and not get deleted.

Is there a way to do that?

Thanks

Ok I can't seem to get the format of the Vlookup formula right for what I am using it for.

I attached a copy of the spreadsheet. What I am trying to do is input a value in cell E7. Then have Vlookup search column L6 to L75 for a match and return the value in column Q next to that match, and put that "score" in cell E9.

Thanks

Hi.

I am trying to use VLOOKUP formula in order to retrieve information from a cell.

I am getting the formula to search for a surname (taken from a cell reference) and then copy the data from a cell three columns to the left of it into another cell.

The problem is that the formula is retrieving the data from the cell above the target cell, although the formula looks right!

Any suggestions?

Thanks,

Steve

Hello,
Office '03 (Excel), Glass Order! (sheet tab) A1:W1 is my header row. The
data range on this worksheet is currently A2:W237. Information is added
daily, so the formula will need to have a dynamic range.
I would like a formula that does something like the following. If any cell
in column T of the Glass Order sheet tab >0, then send that row's information
to the only other sheet tab in this workbook (RMA!), which has the same
format as Glass Order! (header row A1:W1...other than the header row this
worksheet is currently empty, but after this formula will need to be dynamic
also....by adding each new row's information underneath the last ones)
I believe I'll need a vlookup formula to do this, but am not able to get it
right...a little help with the formula would be great! Once I have a
formula, where do I put it? And how should I copy it across the range? fill
handle?
Please help and let me know if I need to better describe what I'm looking
for or if you need more information.
TIA~

I am trying to use Alan Beban's famous vlookups formula to find multiple occurences within an array. I have copied the formula down to accomodate all possible occurrences of the lookup criterion, yet I keep getting the error message "Select at least (x) rows" wherever the looked up value has more than a single occurrence. I'm not sure what "rows" are being referred to in this message since, as I said, I've copied down enough to cover the maximum potential answers. Anyone familiar with this?

Thanks,
Bill

I am using VLOOKUP formulas to identify quanities of multiple items in
multiple workbooks with multiple worksheets. I have created the formulas in
one workbook and would like to copy them all at once instead of indivudually
as there are 80 plus formuals. Any help would be great. Thanks in advance.

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22

May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks

Low

--
A36B58K641

Dear Sir,

I have a problem using Vlookup formula .

The formula does not show blanks for items that are not found in the look up
table.

Lets take the following workheet example :

A B
Item Price
1 5110 25
2 5111 25
3 5112 25
4 5113 30
5 5114 30
6 5115 36
7 5116 36

Look up table
Item Price
20 5110 25
21 5113 30
22 5115 36
23 5120 40

When I enter =VLOOKUP (A1,A$20:B$23, 2) at B1 and copy down to B7, I always
get the answer as illustrated.

The items that are not found in the look up table has the value of the last
item that is found in the in the table.

The formula does not show blank or N/A (not available).

Is there any other way or other formula for getting what I want ?

Thanks

Low

--
A36B58K641

Can anyone show me how I might vary the worksheet name in a VLOOKUP formula by cell?

Let's take a straightforward VLOOKUP formula:

=VLOOKUP($A2,'Sheet1'!$A$2:$G$65536,5,False)

which works just fine. However, let's say I need to do a load of these from many different sheets (imagine sheet2-sheet180). I've got my row 2, columns B:FY populated with:

Sheet1 Sheet2 Sheet3 Sheet4 ......Sheet 180

isn't there a way I could refer to these cells to change the name of the sheet I'm doing a VLOOKUP for? By this, I mean somehow insert a cell reference in my VLOOKUP formula which would change as I copied it across my sheet (Sheet2, Sheet3, Sheet4, etc.), thus saving me the task of manually re-writing each VLOOKUP formula each time.

I haven't had any luck so far, but would appreciate any advice from someone who has done this.

Thanks,

-Pete

Hi All,

This is my first post, so I hope it makes sense. I will try to explain this in as much detail as possable!

I have a VLOOKUP formula which links to an external Excel Workbook which I update each week. I simply copy the previous weekís formula and change the week-ending date to the new date. I use a Product ID for each product to look up against for Units, Sales and GP$. The problem is, I have my spreadsheet set out like this:

Week 1 Week 2

Product 1
Units
Sales
GP$
GP%

Product 2
Units
Sales
GP$
GP%

EtcÖ

I have to change the formula for Units, Sales and GP$ for Product 1 each week and then copy them to all the subsequent products, sometimes which may be made up of 50+.

Is there a way I can have a formula in Product 2+ that says to apply the same VLOOKUP formula as Product 1, but use the Product ID for Product 2 to lookup against?

Any help is greatly appreciated.

Cheers,
Benno.


No luck finding an answer? You could always try Google.