Vlookup References A Table In Another Workbook
In trying to construct a Vlookup formula that references a table in another workbook, I find that when I double click on the tab in the source workbook to insert the name, and then complete the formula with the column reference and a False statement, I keep getting an “Invalid” statement in the fx wizard when I try to solve this problem. By entering the name range by typing, I get a “#NAME?” error when I go to the wizard to see what I’ve done wrong.
This issue was addressed in the link that I searched for in the old help files and submit in this question. It seems that the issue was never resolved in that posting and I can’t figure out how to resolve this matter.
View Complete Thread with Replies
Related Forum Messages:
VLOOKUP With Multiple References
I am trying to accomplish a task that uses the VLOOKUP function to retrieve data from another worksheet. The problem I am having is that it will only return one row of data.
In the formula below, there are multiple rows that contain the $C$4 reference.
I would love to figure out how to return all the rows that contain that
=VLOOKUP($C$4,'Hot Zone Customers'!$C$2:T561,14,0)
Vlookup References Between Worksheets
I have 3 Sheets (Sheet1, Sheet2, Sheet3)
I want to match the value from Sheet3!FundID to Sheet1!classID and Add Corrospinding Sheet3!Fname value to Sheet2(Fname). I tried using VLOOKUP, MATCH & INDEX but somehow not getting the correct formula.
Using References In Pivot Table Calculated Fields Formula
Worksheet A contains two columns, that maps individual operations to their unitary cost:
COLUMN 1 = a list of operations
COLUMN 2 = the cost associated with each operation.
I can do a vlookup on this worksheet to retrieve the cost of each individual operation.
On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.
operation 1 was performed 5 times
operation 2 was performed 7 times
operation 3 was performed 4 times
I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be
= operation * vlookup("name of operation","range for lookup table",2,0)
... unfortunately, "references, names and arrays are not supported in pivottable formulas".
A Formula That References A Table Based On Contents Of Another Cell
Here is what I am trying to achieve:
I have a workbook with two tabs, on the first one there is one column and the top of the column(A1) is a dropdown containing the options 'Boy' or 'Girl'.
One the second worksheet I have two named tables (Boy and Girl) - these tables each contain a single column array with 5 names in each (so we have a table of 5 boys names and a table of 5 girls names)
What I want to happen is when a user selects an option from the dropdown in A1 the cell below(A2) is populated randomly with a name from the corresponding list.
I have achieved this initially using the following formula:
=IF(A1="Boy", INDEX(Boy,RandInt(1,10)), INDEX(Girl,RandInt(1,10)))
This works fine, but I will need to expand this for several dropdown options, so my new workbook will have a dropdown of car makes, and each make will have a list of the models associatied with the car make (e.g. Ford: Fiesta, Escort, Mondeo...)
But I do not want to have a ridiculously long nested IF statement for every car Make, so what I want to do is something along the lines of the following:
=INDEX([CONTENTS OF A1],RandInt(1,10))
So I use the text selected from the dropdown in A1 as the TableName in my formula - however, I cannot retrieve that to use in the formula without it coming back as a text with the "" included and my formula errors!
VLOOKUP With INDIRECT (become Dynamic As The Table Array Part Of The Vlookup Will Change)
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
A Formula In This Workbook Contains One Or More Invalid References
I have a rather complicated workbook with many VBA formulas. When I close it down a window displays "A formula in this workbook contains one or more invalid references". verify that your workbook contains a valid path, workbook, range name, and cell reference.
This only happens when I close the workbook. I seem to have all functionality. How can I find the invalid reference.
Formula That References A Cell Where Workbook Is A Variable
Say cell C5 contains the name of a project (workbook). eg
C5 = "[Project1]"
In my current workbook, I want to find the value in cell B8 of Sheet1 of the project shown in C5. If I wrote this directly, it would be "='[Project]Sheet1'!B8"
But the name of the project is a variable shown in cell C5 (as explained above). So I need a formula that will find the value in cell B8 of sheet1 of the project shown in C5.
2007 Macro Relative References Works On Only One Workbook
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
Copied Worksheet With Macros Still References Original Workbook
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
For i = 12 To 20
Set curcell = Worksheets("Form Generator").Cells(i, 3)
If curcell = False Then
Cells(i, 3).Value = True
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
Changing Hard Coded Column References To Variable References
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
Structured References And With Cell References I Get A Column Of Zeros
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
Bug In A VLOOKUP Table
I have a VLOOKUP table with 805 rows and 88 columns (A5 to CJ805). When asked to display the figure in column 84 (CF) relative to that in Column 1 (A), i.e. for a figure in Cell A75 it should display CF75, it sometimes displays the figure which is 1 row above, i.e. CF74.
VLOOKUP Table Ranges
Is there anyway to, instead of indicating specific cells to be included in the range of a VLOOKUP table, designate the table range as whatever cells are selected in a spreadsheet.
The VLOOKUP formula is in a spreadsheet called "Template". The VLOOKUP table range will be in a spreadsheet called "Statement of Assets".
Here is the formula I have:
=VLOOKUP("Lookup Value",'Statement of Assets'!$A$122:$C$142,2,0)
But the row numbers in "$A$122:$C$142" is going to change depending on what plan number I am working on. Columns stay the same.
What I want is something like:
=VLOOKUP("Lookup Value",'Statement of Assets'!whichever cells are selected,2,0)
Vlookup Function To Look Up In A Table
I need to create a summary table of dates and data. The trick is I need the vlookup function to look up say 7/1/08 in a table that all the dates are of the format 7/20/08 and so on. Is there a way to have the vlookup return the proper data by ignoring the day.
Table Refererences In Vlookup
Is it possible for me to use the contents of a cell to change what table is being looked at in a vlookup?
E.g. lets say i have a bunch of data which is split into months with each month having it's own table. In cell L3 i have a formula to display the current month from today's date from H3 (using =today()).
Therefore when using a lookup i want to refer to L3 which effectively has in the title of the table i want to look at.
VLOOKUP Vs Pivot Table
In creating a new sheet to allow for multiple piece of information to auto fill; I am struggling on a small VLOOKUP issue. On my parts information page (where the info is being pulled from) I want to be able to separate out data based on the manufacturer while still leaving enough open/blank space for future additions. My issue is that with VLOOKUP every time I add to the current list(s) of data, I have to re-sort to alphabetic in order for it to work right. This means that the separation I am looking for disappears for the most part. Maybe I am doing something wrong or I am missing something, which if so, do Pivot Tables work better for this situation?
How my sheet is working is the user can pick from a list of items, click a button and the item is then transferred to another page, when it transfers the corresponding cells on the transferee page will auto fill with the rest of the info in those cells, thereby displaying not only the item picked but all of the realative data with it.
Vlookup - Value Not In First Column Of Table
Is it possible to use the VLOOKUP (or similar function) when the value it is looking up is not the first column.
A B C
Kevin 8.00 140000003
Paul 8.00 140000001
I want to lookup the value in column C and return the value in column A. Kind of the reverse to this =VLOOKUP(E1,A1:C2,1,false)
Conditional Vlookup Return Table
Main (Sheet1) - Got a table with several informations relative to a number of a house
House1 (Sheet2) - Want the information extracted from Main (Sheet1) to a table.
House2 (Sheet3) - Want the information extracted from Main (Sheet1) to a table.
So basicly i want to extract the info from Sheet1 if it matches the Number of the House on sheet2.
With Vlookup can only get a cell value as i am not an expert in excel
See the attachment
Vlookup, Using A Cell That Contains The Table Reference
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE)
I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE)
This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
VLOOKUP, IF Condition And Pivot Table
Have following table:
No. Be Country
1 D GB
1 C DE
2 B DE
2 A GB
I want to present it as follow:
1 D C
2 A B
Did not solve it with a pivot table. Not either with VLOOKUP combined with IF. I am thinking in the direction: If 1 in the first column and GB in the third, return the D. But I am not getting it right.
Data Entry Into Vlookup Table
I have a workbook where in Sheet1 Cell A1 is a customer name, and A2:A5 contain parameters for the customer. I would like to be able to input those data to a Vlookup table on Sheet2. (The Vlookup is used to fill values on Sheet3).
I have been trying to create a macro to copy those parameters from sheet1A2:A5 by finding the match for A1 in the Vlookup table in Sheet2 and then copying to the corresponding matching row , A2 pasting into column 3 (on the corresponding row of course), A3 into colum 4, A4 into column 5 and A5 into Column 6.
VLOOKUP Against Pivot Table In Macro
one part requires a pivot table to split two seperate pieces of data. the macro is running fine until this point when i get the AddFields method error (run time 1004). the Visual Basic error is as follows:
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Employee", _
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Lab Hrs")
.Orientation = xlDataField
.Caption = "Sum of Lab Hrs"
.Function = xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveCell.FormulaR1C1 = "Int Lab Hrs"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-25],Sheet1!C[-26]:C[-24],3,FALSE)"
is this basically a write off or can anything be salvaged to make it run correctly. the pivot table will always use the same columns etc. every month it is run.
Creating A Ranked Table Using VLookup
This is a spreadsheet I'm using for my fantasy basketball draft that by adding information into table C3:N15 will populate stats into team sheets and output average stats into table D20:L31 and then create a rotisserie style scoring table outputted into B36:M47.
What I want to do (and have tried to do in table B51:M62) is output this to create a sorted ranked by Approx Score without using macros or VBA. I also want to do this using Excel 2003 rather than 07 (I'd love to take advantage of the AVERAGEIF function but i figure compatibility is my first concern).
The issue I have run into is that tie scores are going to make it difficult, and utilizing a table w/ summed ranked will lend itself to that happening...a lot. How can I solve this?
Vlookup Table Array Variable
=VLOOKUP($B$54,'R:OperationsPerformance ReportingSales ReportsFTW North - INT2006Daily7 July7-02-2006[Sales TL Base Report.xls]MAXimize Summary'!$A:$BA,E$3,FALSE)
I would like to make date in the table array (07 July7-02-2006) a variable, but keep getting an #NA with my limited experience. From what I can gather the single quotes are causing the problem?
Calculating Average Using Vlookup Table
i have data on excel which has the following fields:
Invoice No,Inv.Date,Month,Customer,Part Number,Description,Qty.I want my final pivot table to have the following results for every part no. and description
Total monthly sales
total year sales
and for each model sale calculated as sale quantity / week
I have tried the following way but it did not work:
just next to the pivot table under the heading"AVG" i used the following formula to get the sale/week on cell o6=grand total sale /(applicable no. of weeks).and when i am copying the formula to the cells lying below the formula is being applied as that of the first cell so getting the same result for all the rows.is there a way to calculate sale/ wk within the same table
VLOOKUP Based On Date In Table
I have a table, which is a price list, with different prices based on different dates. For instance, the dates may be 1/1/04, 6/1/04, 7/1/04, 10/30/04, etc. I have some dates in another table, for instance, 2/2/04, 7/5/04, 12/20/04, etc. I want to lookup the date closest to, but not greater than, the last previous date posted. So, for the first date of 2/2/04, you would use the column with date 1/1/04 from the first paragraph. For a date of 7/5/04, you would use the column with date of 7/1/04. I think this is a simple VLOOKUP function, but not sure how to do a date compare. Also, the lookup has to do two things. First, it has to find the part number in the table, and then match it do the respective most recent date.
Part Number ---- Date
5555555555 ---- 7/5/04
Part Number ---- 1/1/04 ---- 6/1/04 ---- 7/1/04
5555555555 ---- $65.32 ---- $68.52 ---- $71.52
I want to grab the price with the respective date from the first table. In this case, it would grab the price of $71.52 from the lookup table.
Help With Variable Table Array In Vlookup Formula
I'm working on a project in Excel (for a restauraunt) and I basically need to make a formula to work out what an item is, using it's ID number and referencing the certain menu that it is in.
as you can see the table array is defined in cell E3, but i still get a #N/A result, and when I replace the "E3" in the formula with "Deserts" it produces a result.
Double Vlookup For Table Array With Dates
I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.
For example, I need to pull the value for campaign 'notset' on 6/16/09.
I tried the following, but it would only work on the first and last campaign: ...
VLookup/Index (round-down E3 And Find Its Corresponding Row In The Table To The Right)?
I need a formula for F3 that will round-down E3 and find its corresponding row in the table to the right and find its intersection with the coating listed in I3. Does that make sense?
I've tried, to no avail:
Looking Up Value In Table (multiple; Unique Id; Columns For No Vlookup)
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
Reference A Cell For Tab Name In VLOOKUP Table Array
Sheet 1 contains:
Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)
Sheet 2 contains:
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
VLOOKUP: Table Array Is Several Thousand Rows
I'm using a vlookup formula, and the thing I'm searching by is a part number. The problem is that in my table array, all the cells for part numbers have that green error flap in every cell, saying "The number in this cell is formatted as text or preceded by an apostrophe."
Some part numbers have letters on the end of them, ie: 12345N. When I go into a particular cell's error drop down menu and choose "Convert to Number," the vlookup will work with this cell. My problem is that the table array is several thousand rows, and I don't know how to fix them all at once.
Change Vlookup Table Array Values
I am trying to do a vlookup where the values in the Table_Array have a string appended to them. For example:
I want to look up the string "Example1" and the lookup range is
Example1 - monthly
Example2 - weekly
Example3 - weekly
Example4 - monthly
So essentially I want vlookup to only look at the string before the hyphen. My initial thought was to do something like this: vlookup("Example1", left(A:A, 8), 1, FALSE)
Making A Table Array In A VLOOKUP Variable
I have a spreadsheet where there are multiple records for each person. Each subsequent record contains information about one year prior to the one above it. So the first records contains info for 2005, the next 2004 and so on. I have the following formula in a cell in sheet 1: VLOOKUP($A2,'Sheet1 (2)'!$A$1:'Sheet1 (2)'!$DD$8796,11,FALSE)
I want to copy the VLOOKUP formula to the cell next to my original cell and have it begin searching on the first row after the first record and continue for each cell to the right. For example, if the lookup in my first cell (say A1) find the result on row 20, I want the lookup formula in B1 to start with row 21. So the change on the formula above would have to change the $A$1 above to $a$row below the first row found. I have a formula which tells me about the first instance of the vlookup finding the first row with the unique identifier.
Move Lookup Table To Same Sheet As VLOOKUP
One other common problem is storing the Lookup Function on another sheet to the Table. On The Uploaded Workbook. Is it possible to change the Vlookup to the same worksheet as the lookup list to remove the disadvantage of having them on separate worksheets. If I have misunderstood the statement in the quote above.
Postage Calculator Using Vlookup On A Table Array
I am doing a postage calculator to calculate postal rates for posting.
However, I am facing some problems. In the Main Sheet, Postal rates, I need to do something
1) We are divided into Zones - 1 to 4
Can I do a Variable that if I type Zone 1 and it will Vlookup on Zone 1 sheet and if I type Zone 4, it will Vlookup on Zone 4 sheet
2nd problem is that:
The postage needed: When I did a Vlookup, when I key in 55, it will select the wrong price table, if you take a look at the attached spreadsheet, 55g for Zone 4 it came out $2.05 but it should be the following rate which is $2.40.
Continue Searching A Table With Vlookup Despite Finding One Match
Im using vlookup to search a table and fill out a calendar with dates that various employees are gone. They can have multiple trips in one month and thus will be in the table multiple times. I have the calendar set up so days go across the top and names go along hte left side. so each cell is identified by name and day. I then have each cell using the vlookup command to search the table. now if one person has multiple trips a month is there a way to compare multiple entries in the table?
Nesting VLOOKUP In IF/vice Versa & Pivot Table
I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).
Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).
My problem is twofold:
1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).
2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.
Vlookup- Table That Has Data Which Uses A Number Which Correlates To An Item
I am using a vlookup with a table that has data which uses a number which correlates to an item. A person types the number into a cell which triggers the vlookup. See example
So..if someone enters let say number 36 into the look up cell..since 36 does not exist in the table the vlookup returns the value next to cell 37. Is there a way to use vlookup so that if a number it is looking up does not exist it wont return the next value? I would like to return an error message if the number being looked up does not exist.
Vlookup: Search For Value In SECOND Column Of Table Array And Return Value From FIRST
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
Copying Vlookup And Maintaining Original Table Array
I'm trying to copy a vlookup to an entire column, I want to look up a value in the cell to the left and compare it to a given table; but when I copy it down it up dates the cell value, but it moves the table down by one row at every row so it's not finding most of the values the further down I go.... what am I doing wrong. I doubt I'd have to re write the formulas in all the 2500 cells I need to look up.
Vlookup Table To Lookup Data In Columns And Rows
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears
9999 2004/5 5 4
9999 2005/6 7 8
9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
FY Apples Pears
2004/5 5 4
2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.