INDEX And MATCH With Same Table On Multiple Sheets?
Aug 25, 2014
I have a workbook (workbook1) with multiple sheets (sheet a, sheet b, sheet c), all with the same column headings. One of the columns in each sheet of this workbook contains an invoice number.
In a different workbook(workbook2) I need to find the invoice details based on sheets a-c from workbook1.
So, in workbook2 I can input the invoice number in column a and the rest of the details will be pulled through based on whichever sheet (a-c) from workbook1 that the details are in.
Workbook1 is normally closed (from reading others posts, the INDIRECT function might do what I need but would not work with workbook1 closed)
Working with just 1 sheet in workbook 1, the following formula works perfectly:
=INDEX('[workbook1.xlsx]sheet a'!$V:$V,MATCH(A2,'[workbook1.xlsx]sheet a'!$W:$W,0))
How to do the same, but looking in sheet a, b and c at the same time for the answer?
View 2 Replies
ADVERTISEMENT
Mar 13, 2014
I've got a problem where I am trying to return a value if two criteria from one table match two from another.
I have included a example of my issue.
=INDEX(Time!D:D,MATCH(Data!A2,Time!A:A,0)*AND(MATCH(Data!C2,Time!C:C,0)))
I have had a look around lots of forums but cant get my head around what is wrong with my formula
View 4 Replies
View Related
Sep 3, 2012
I have a set of values in column A in sheet 1 and a compilation of some of the same set of data in column A in sheet 2. Using the index match function, I have been able to list the corresponding values in column B of sheet 2 onto column B of sheet 1 (for those values which are common to both sheets - matching occurs between column A in both sheets). However, I now have some of the set of data in sheet 2 and the rest in sheet 3 (again, in column A). Is there any way of using index match to search in both sheets and index the corresponding value from column B of the sheet containing the matched value in column B of sheet 1?
I have tried this by nesting the index match functions for each sheet into two separate IF arguments but haven't had any luck. Is there a better way of doing this?
This is what I have currently tried in Cell B2 of sheet 1:
=IF(INDEX(Sheet2!$B$2:$B$3001,MATCH(A2,Sheet2!$A$2:$A$3001,FALSE),1),IF(INDEX(Sheet3!$B$2:$B$3001,MATCH(A2,Sheet3!$A$2:$ A$3001,FALSE),1)))
Excel keeps saying this formula has errors but I have no idea as to what changes I need to make to get the formula to model my situation. Then again, I'm not even sure if this is the approach I should be taking to create a formula that models my situation.
View 1 Replies
View Related
Aug 19, 2008
I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed
View 9 Replies
View Related
Jan 21, 2014
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
View 3 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
Jul 7, 2014
I'm not finding much on INDEX MATCH ASCEND BETWEEN 2 SHEETS and 2 COLUMNS, other then tediously do each pair of columns VBA Asecend, which is what I am trying to avoid.
Sheet1 Column A2:A26 and Sheet1 Column B2:B26 is the source
Sheet2 Column A2:A26 and Sheet2 Column B2:B26 is the the Ascended of Sheet 1
The Reference to Ascend values is from Sheet 1 B2:B26, but must match the same in Column A
So if A2: A26 is, 1,2,3,4,5....and
B2:B26 is and asortment of negative, 0 and positive values, then the lowest value is to be shown first on Sheet2.
-----------
Sheet 1
A B
------------
1. 10.
2. 5
3. 0
4. -15
5. 3
then Sheet 2's INDEX is:
-----------
Sheet 2
A B
------------
4. -15
3. 0
5. 3
2. 5
1. 10
If blank, show "",
View 10 Replies
View Related
Jul 16, 2014
Question about what I think should be accomplished using an index match.
On Sheet1 I have values in O3:O132.
I want to find these values on Sheet2 in I2:I197
Then I want to return the matching value with column AL's corresponding value which are also in row 1 in columns J1:BM1 on Sheet2.
View 6 Replies
View Related
Jan 12, 2009
I have a table with the months from B1:M1 & Customers from A3:A35. B3:B35 are transaction numbers. On another sheet, I have a cell, C2 with the name of the month that I type in, I've named it MONTH. I also have a seperate section for each customer that NBVC has helped me populate from another sheet based on MONTH and their actual transactions. At the bottom of the section, I want it to populate from this other table based on the month in the MONTH cell.
View 2 Replies
View Related
Feb 16, 2014
I have a Pivot table (see picture) and in another field I want to be able to type in the Month and Year (e.g. January 2012) and return the worst performer of the 8 suppliers. I have been trying to use SMALL in conjunction with INDEX(MATCH(MATCH)) but it doesn't appear to be working.
I am sure there is a simple solution but it is evading me at the moment.
excel.jpg
This is where I am at with the formula. Maybe I can use the IF function somehow?
=INDEX('Pivot Tables'!B19:I19,MATCH(Summary!G3,'Pivot Tables'!A20:A41,0),
MATCH(SMALL('Pivot Tables'!B20:I41,A5),'Pivot Tables'!B20:I41,0))
View 4 Replies
View Related
Jan 19, 2009
I have a number of identicle tables, that have all been seperatly named. On a summary tab i am using an index match formula to pull back key pieces of data from the table that is identified by cell B2. So if in B2 their is SCB, i would like the formula to apply to the table SCB. The formula (when useing the word SCB works fine):
View 2 Replies
View Related
May 20, 2009
Need in index and match function from a pivot table. I have enclosed an attachment with the sheets that I use. "Data" - contains the data, "Pivot" is the pivot from the data, and "Output sheet" is the sheet where the cells need to be populated.
Basically what I would like to do is to take the pivot and then match the currency, the time indicator and thereby also fill in the value for the matched cell. I have been racking my brain how to do this and I fail miserably.
View 2 Replies
View Related
Jan 8, 2013
My VBA currently reads a weekly updated table and basically dumps it to the end of a masterfile table...however I need to add a small section that will then go back to the beginning and line by line copy one cell in a single column to the same table but to the previous year.
i.e.
G8:G12345 contains this weeks sales which have already been added to the bottom of the masterfile (along with lots of other data)
A8:A12345 contain a barcode
B8:B12345 contain a date
What I now need to do is lookup the barcode and Date -1 year (so 2013 becomes 2012) and write the same data to column AG(n) for 2012. I could use formulas to lookup the data in the Master Table but I am trying to keep this table free of any formulas.
It's the match and index, and write bit I'm struggling with, looping through the inital data is not causing me any problems.
View 6 Replies
View Related
Feb 13, 2013
I regularly use the Index/Match formula. I just noticed, when indexing a table of subtotals, the match doesn't necessarily pull in the subtotal, but rather pulls in the first instance found in the rows that have been subtotaled. Is there a way to index/match the subtotal table to force the match to only view the subtotals, not the rows grouped to create the subtotal? I know I can manually, copy/paste the subtotals to "remove" the grouped data from the index range, but was hoping for an easier solution.
View 3 Replies
View Related
Jul 27, 2014
Look at the image I've attached.
I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.
testtable.jpg
View 13 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
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
Dec 2, 2013
I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.
The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And I want it to extract "Bills" value from "rule" sheet.
There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.
How do i solve this problem with index-match function?
View 3 Replies
View Related
Mar 28, 2012
I tried searching some other Index/Match threads but figured I did not want to confuse the other original posters. I can index/match on a single set of criteria but what I am trying to do it add an additional match criteria into the equation and that is the last "MATCH" in my formula pasted below (MATCH($B3&"",O1:S1,0).
1)I need to match the value in Column A to Column N
2)Then for that combination, I need to match the combination value in Column B which ranges across Column O thru Column S
a.Ex. Cell A3
i.0001 matches to 0001 and B3 #50000 pulls in the value from Q3 (which would be 2)
Formula in J3
=INDEX($N:$S,MATCH($A3,$N:$N,),MATCH($A3,$N:$N,),MATCH($B3&"",O1:S1,0))
View 4 Replies
View Related
Mar 26, 2009
I have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
View 7 Replies
View Related
Mar 7, 2013
I am trying to return a reference to the first cell that meets several conditions. I can calculate a column (say Col E) that tells if each row meets condition with
=IF(AND(IF('number of cases by year'!B28:B547>'cumulative distribution >0 '!F51,TRUE,FALSE),IF('number of cases by year'!$A$2:$A$521>=DATE('% of cases captured'!$C$3,'% of cases captured'!$A$3,'% of cases captured'!$B$3),TRUE,FALSE)),TRUE,FALSE)
Then to find the first time this is met I use
=MATCH(TRUE,INDEX(E2:E521,0),0)
Is there a way to do this all in one step? I tried
=MATCH(TRUE,INDEX(IF(AND(IF('number of cases by year'!B20:B539>'cumulative distribution >0 '!F43,TRUE,FALSE),IF('number of cases by year'!$A$2:$A$521>=DATE('% of cases captured'!$C$3,'% of cases captured'!$A$3,'% of cases captured'!$B$3),TRUE,FALSE)),TRUE,FALSE),0),0)
but it does not seem to work. It returns #VALUE
View 1 Replies
View Related
Jan 10, 2014
To summarize - Columns B through E are current MLB Players who I am trying to compare with the data in Columns G though J. My goal is to both use index and match to find the following in Column L: A comparable player name using the criteria of having an exact match for both position and age, but the closest approximation match between Columns E and J.
I have deleted a significant amount of player data to be able to upload the document on this site.
I am trying to find a way to combined exact and approximate matches in one single formula, but have been unsuccessful thus far.
View 10 Replies
View Related
May 23, 2014
I am making a table that reads from an list of employees. I have attached a sample sheet (changed the names). The list includes Name, 2014 Start Date, Pay Type, Job Title, Location, Weeks, Hours, Avg. Hours.
I need a formula that will list employees on a separate sheet based on the following criteria:
2014 Start Date = 1/1/2014
Avg. Hours >=30
I have worked with a number of INDEX MATCH combinations but I cannot get the formula to quit at 30 Avg. Hours whenever I drag the formula down. I also have seen some employees that average over 30 hours get "skipped" over whenever I drag the formula down.
View 5 Replies
View Related
Jun 8, 2014
i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?
e.g. worksheet 1 has all the data
A B C
email ID date
worksheet 2 has a list of specific email addresses i'm looking for info on
A B
email date
My formula in column B of worksheet 2 is along the lines of this:
index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))
basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.
The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.
View 6 Replies
View Related
Oct 7, 2013
I am trying to create a formula to pull in the mgmt fee% into the investor capital forecast tab, based on two vaiables. The client's AUM(column B), and their Tier (column A). It should pull in 1.157% from the Mgmt fee schedule tab, based on AUM of 314MM, and Tier 3.
indexmatch.xlsx
View 3 Replies
View Related
Jan 16, 2012
I am trying to get a formula to work that looks up a vendor, and then checks the quantity of their order (less than 99999999, less 500, less than 200, and less than 50). I get either a #NAME or #N/A when I try to use what I have written. I tried to include an =index(array,AND(Match(row 1,exact match),Match(row 1 less than quantity),Match(row 1,greater than quantity)),Match(rate,exact match)) returning the variable rate. I have had no luck using vlookup and pivot tables.
View 6 Replies
View Related
Aug 1, 2012
I am trying to do an index match with multiple criteria and it keeps on returning a 'False' value. I am also not sure if it is adjusting the indexmatch lookup values for each row.
Code:
Sub Check()
Set ws1 = ActiveWorkbook.Sheets("SprocketPartData")
Sheets("SprocketPartData").Activate
Set ra = ws1.Range(Cells(2, 4), Cells(65536, 4).End(xlUp))
Dim c As Range
[Code] ........
View 7 Replies
View Related
Jul 22, 2013
Basically, I want a code that does a simple index-match function for a column - But for all the #N/A's that come up in that column, I'd like it go to another INDEX-MATCH function - and another after that. I'm trying to keep it all in one column.
Here is an example of the first index match
=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(L2,[FILENAME.XLSX]DealerDetails!$CJ$2:$CJ$15000,0))
If this turns out an #N/A, I'd like those #N/A's to switch to this formula:
=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(M2,[FILENAME.XLSX]DealerDetails!$CK$2:$CK$15000,0))
Then the remaining #N/A's to go to this one:
=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(G2,[FILENAME.XLSX]DealerDetails!$G$2:$G$15000,0))
The column I'm referring to will have around 2,000 records or less.
View 1 Replies
View Related
Dec 15, 2013
I have 2 workbooks and would like to use an index match formula to populate the data in the second workbook. I wanted to populate TOTAL HOURS in workbook 2 by matching "Month To Date" and "Employee ID" from Workbook 1.
Workbook 1
A
B
C
D
E
F
1
Supervisor
First Name
Last Name
Employee ID
Total Hours
[Code] ...........
View 9 Replies
View Related
Apr 3, 2014
I have two files
Say first file name is X and in work sheet 1 i have used name manager for two columns say "Series" and "Fund name"
Second file name is Y
R7C3 is Fundname of Y file
RC4 is Series Value of Y file
In the row of Y file , C 15 - I want to calculate the formula
Range("C15").FormulaArray = _
"=IF(ISNA(INDEX(Series,MATCH(R7C3&RC4,Fundname&Series,0))=RC4),""NO"",""YES"")"
I am not getting any error but the result is not correct , even for the series which is not there in my file "X" it says "YES"
See my codes below :
Dim colA As Variant
Dim colB As Variant
Dim WksDash As Workbook
Dim Wksfile As Workbook
Dim wksWatch As Worksheet
Dim WksDash2 As Worksheet
Dim Classunion As Variant
[Code] .....
View 2 Replies
View Related
