Excel 2010 :: Index / Match With Reference?
Dec 24, 2013
I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).
When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.
**Why does it do that and is there a way to stop it?
Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.
I am using Excel 2010 on a company computer with the macros blocked so no VBA.
Code:
=SUM(IFERROR(INDEX(Mon!$D$6:$D$225,MATCH("2000",Mon!$E$6:$E$225,0)),0),
IFERROR(INDEX(Mon!$D$6:$D$225,MATCH("2000",Mon!$N$6:$N$225,0)),0),
IFERROR(INDEX(Tue!$D$6:$D$225,MATCH("2000",Tue!$E$6:$E$225,0)),0),
IFERROR(INDEX(Tue!$D$6:$D$225,MATCH("2000",Tue!$N$6:$N$225,0)),0),
IFERROR(INDEX(Wed!$D$6:$D$225,MATCH("2000",Wed!$E$6:$E$225,0)),0),
IFERROR(INDEX(Wed!$D$6:$D$225,MATCH("2000",Wed!$N$6:$N$225,0)),0),
Excel 2010
View 9 Replies
ADVERTISEMENT
Jul 18, 2014
I have a victim of the Index-Match duplication problem in Excel (2010). Basically, I have three columns of data, all daily input for the year.
Column 1 = Date
Column 2 = Actual (Units Sold)
Column 3 = Scheduled (Units Sold)
The Date is filled out through the end of the year as is the Scheduled values.
The Actual values are filled out daily.
I need to generate a summary box that reports Actual, Scheduled, and Variance (Actual - Scheduled) for the time periods Daily, Month to Date, and YTD.
My problem is that when I try to return the Schedule value that corresponds with the date of the last entry, I don't know if I am pulling the correct Schedule value since I do not know if the Actual value (that is pulled from the last value in the Actual column) is unique. So I tried using an Index-Match formula to return the latest value (that is the last record occurrence of the value) to my function in order to retrieve the correct Schedule value, but, sadly, it did not work.
I by no means am an Excel expert like many of you, so I may have some questions along the way.
I've attached a sample extraction from my worksheet and included an example of the Summary panel I'm creating.
ActualVsScheduled_problem - Copy.xlsx
View 14 Replies
View Related
Sep 13, 2012
The context of this problem is that I am pulling sales data from different outlets to analyse monthly sales. The data from the various outlets are in various formats and the products, even though they are the same throughout all the stores, are named differently. For example, Mushroom Soup can be named as "Soup Mushroom", "Mushroom Soup" or "Cream of Mushroom". This makes analysis really tough.
I first created a table with a list (K6:P6) that had the varying names of the products. This is shown as the first table with 3 rows.
Then I tried to match this list to a range of products sold by an outlet e.g. Electric. (shown in the next bigger table $D$10:$D$254, ) Next, I used Index to call up and display the sales of that product next to table with list A ($G$10:$G$254)
This works for some entries but not for all. I get mostly #NA answers even though I do ctrl-shift-enter. Also, the bigger table is in a separate file and I am using excel 2010.
=INDEX('[GON-JUN 2012.xls]gon-jun'!$G$10:$G$254,MATCH(K6:P6,'[GON-JUN 2012.xls]gon-jun'!$D$10:$D$254,0))
[URL]
View 8 Replies
View Related
Oct 3, 2013
Whatever formula I try is returning an error of #N/A
I have two worksheets the first with transport data - customer,collection point, delivery point, haulier and number of pallets - all this information is in columns; the second sheet with the rates show haulier, customer, validity, delivery point in columns and then pallet numbers run across a row with rates tying in underneath. i have simplified this below.
I am just trying to reference all the data and work out what the cost for the variables would be in the example the cell i'm trying to find is H8 (320) -
I have already tried the following and several modified versions:
=INDEX('RATE LOOKUPS'!$E$6:$I$9,MATCH(1,('MASTER DATA'!A:A='RATE LOOKUPS'!C:C)*('MASTER DATA'!B:B='RATE LOOKUPS'!B:B)*('MASTER DATA'!C:C='RATE LOOKUPS'!D:D)*('MASTER DATA'!D:D='RATE LOOKUPS'!A:A)*('MASTER DATA'!E:E='RATE LOOKUPS'!E5:I5),0))
[Code] ..........
View 6 Replies
View Related
Jan 7, 2013
Using Excel 2010.
Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.
Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.
I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.
Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.
View 1 Replies
View Related
Feb 14, 2014
I am using Excel 2010.
My objective is to pull specific values from an external file corresponding to the correct name and year of my choosing. The first way I thought would be best is to use an INDEX/MATCH function. The problem is the external files change names so I would need a method to easily change the source file name from one cell. I then stumbled across the INDIRECT function, but the INDIRECT function will only work when the source file is also opened. I then came across Harlan Grove's pull function which allows you to import data from closed excel files.
So, this is what my formula looks like: =INDEX(pull("'"&G12&"");MATCH(C15;pull("'"&G14&"");0);MATCH(D15;pull("'"&G16&"");0))
Cells G12,G14, and G16 contain the file paths for the ranges. C15 is name and D15 is year.
The problem I have though is that when I try to execute the function, Excel gets stuck. if the code cannot handle large amounts of data. I tested the code with a simple SUM function for a small range from an external file and it worked just fine.
This is the code I am using:
[Code] ...
View 3 Replies
View Related
Jan 20, 2012
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
Here is an example of the formula:
Code:
=INDEX(Table1891034445678912822253536[#All],
MATCH($I35,Table1891034445678912822253536[L/I],0)+1,
MATCH(L$34,Table1891034445678912822253536[#Headers],0))
Just look at that Table name!!
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
View 2 Replies
View Related
Jul 18, 2014
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,
INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))
View 9 Replies
View Related
Nov 1, 2011
I am trying to use a match index formula to retrieve some data from another workbook...I am wondering if I can use a worksheet name as an IF condition, i.e. I want my match index formula to search the entire workbook, and return values for which there is a match, with the IF conditions being a specific code AND worksheet name.
View 1 Replies
View Related
Jun 11, 2014
I am trying to work on a index match but can't seem to make it work.
My look up value are on column while the data I want to show and look up array are on rows and still getting 0 results.
Is there any solution ofr this to make it work without altering my look up value & arrays to columns as well?
View 4 Replies
View Related
Jun 21, 2012
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
View 1 Replies
View Related
Jun 6, 2006
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
Mar 5, 2012
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
View 1 Replies
View Related
Dec 28, 2009
I have the following formula in a cell:
=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9
When the workbook with this formula is open it slows down other workbooks quite a bit.
If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.
How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.
View 9 Replies
View Related
May 22, 2014
I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.
On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.
How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?
I know a simple vlookup function will return the values that it matches first and that is not what i want.
Example: Sheet1
Column A (Sales Order#) | Column B (Quantity)
0417436GPCP | 1
0417436GPCP | 1
0413412FACY | 1
0413412FACY | 1
[code].....
View 6 Replies
View Related
Jul 30, 2014
I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.
Worksheet 2 - 63000 rows
Worksheet 3 - 48000 rows
Worksheet 4 - 23000 rows
Worksheet 5 - 21000 rows
In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.
=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI
[code]....
View 4 Replies
View Related
Sep 21, 2012
HTML Code: VLOOKUP(M2,'FX Rates'!$A$1:$P$199,2,FALSE)
I have lots of vlookups in my spreadsheet. Match and Index is a better method and should speed up my spreadsheet (1000s of vlookups!).
View 5 Replies
View Related
Feb 28, 2013
excel 2007. Here is the situation:
I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:
A
B
C
D
[Code].....
So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.
The formula I have at the moment is this:
{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}
How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?
View 3 Replies
View Related
Mar 26, 2013
currently using Excel 2007 with Windows Vista.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B
C
D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
View 2 Replies
View Related
Jul 12, 2013
Basically, I am trying to write a program that will index match through a range and if it doesn't find the value that it is matching to, it will add the value to the bottom of the range it is indexing against. In other words:
My C column has a list of CUSIPS. In column J, I would like for the User to be able to add a list of cusips and then have the ones that don't already exist in the list of CUSIPS in column C be added at the bottom of the range. I have written a macro now that uses the index match, but for some reason it adds all of the cusips that exist in J range to column C, not just the ones that are missing. I can provide a template if necessary.
Sub Filler()
Dim Row As Variant
Dim NumberOfRows As Long
Row = 0
[Code]...
View 4 Replies
View Related
Oct 4, 2013
Excel 2003
I have a table with a header row of scores 1-10 in B4:K4.
In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.
What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...
This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?
View 9 Replies
View Related
May 23, 2014
I have one worksheet. I am needing to match up column G cells (TELCEL/MULTI) with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.
column E
# DE EVENTO
COMPANIA
INICIO
MES
Clave del producto
Descripcion
Fecha del movimiento
Clave de la tienda
Clave de la caja
Clave del usuario
Clave del vendedor
Tipo de movimiento
row3
TELCEL
[Code] ........
I have to put 1 to first and then sum one if condition is true
1
TELCEL
TAT
may
TAT31
Telcel, Telcel tiempo aire $31
01/05/2014
01
01
3
Ventas
View 4 Replies
View Related
Jun 10, 2014
I'm having trouble using wildcards for text in index/match multiple criteria; I need to find the nearest site along a river (x) below a certain point (distance =21), each stretch of the river is labelled with the streches downstream (so stretch abc is upstream of ab).
My problem arises when the nearest point downstream is on the downtsream stretch of river. How do I use wild card to search for ab&"*" that will exclude abd.
River
Distance
Stretch
S1
x
10
a
[Code] ..........
'formula in cell E14 ={INDEX(A2:C11,MATCH(1,(A14=B2:B11)*(C2:C11
View 2 Replies
View Related
Nov 23, 2012
I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.
I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004
I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.
'Daily Entry Sheet'!$B$8:$B$138 = Date
'Daily Entry Sheet'!$F$8:$F$138 = Property #
'Daily Entry Sheet'!$G$8:$G$138 = Property Location
'Daily Entry Sheet'!$H$8:$H$138 = Work Order Number
Following is what I presently have coded.
The following Works but is not completely as I need.
=IF(OR($F8=0,$F8="",ISNA(G8),$H7="Work Order #"),"",IF(ISERROR(INDEX($H7:H$8,MATCH($F8,Prop.,0))),"",INDEX($H7:H$8,MATCH($F8,Prop.,0))))
This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.
I looked for a function similar to the sumif or countif
=SUMIF(Prop.,$F4,WorkOrdNum)
=SUMIF(Date,"="&$V6,Total_____Hours)
But I don't need to add the work order numbers.
Countif can give me how many instances property # 919 is used,
But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.
View 9 Replies
View Related
Mar 15, 2013
I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.
Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.
View 4 Replies
View Related
Jun 4, 2014
Excel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.
I modified another poster's script as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Move rows with negative value in E to Flagged Items sheet
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("E2:E200")
[Code] ........
The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.
I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.
Is this possible in the script or is there another way to achieve the desired result?
View 4 Replies
View Related
Aug 7, 2014
I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.
View 2 Replies
View Related
Jan 29, 2014
I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I am using Excel 2011 for Mac
View 7 Replies
View Related
