Index Single Occurrence With Multiple Match
May 30, 2012
Given a database as follows:
In A40:
HTML Code:
kindNo.IDSiteAOCOptdMOBMOSShsTAOSDOO
OTH11180MCF411.9296170T2/1/10none16 mos5/4/12
OTH11180MCF411.9208170T2/1/10none16 mos5/5/12
OTH11181DDF415.64132T1/31/08none36 mos5/6/12
OTH11181ESF415.68132T1/31/08none36 mos5/7/12
OTH11182PDF47.94132T1/31/08none44 mos5/8/12
bop11183BPF413.722135T3/6/08none37 mos5/9/12
[code].....
I would like to retrieve all No. from ColumnB if the following 3 conditions are met:
1 Column A "kind" is "oth"
2 values in Column K "DOO" are superior and equal to Cell B2:01-may-12
3 values in Column K "DOO" are inferior and equal to Cell D2: 31-may-2012
Formula presently used in B4 is:
Code:
=IFERROR(
INDEX($B$41:$B$52,
SMALL(IF(($D$2>=$K$41:$K$52)*($B$2
View 3 Replies
ADVERTISEMENT
Jun 25, 2008
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
View 2 Replies
View Related
Feb 20, 2011
I have tried using the 'arbitrary lookup' function as listed here but can't seem to get it to work. [URL] .....
I have a list of all football fixtures and results in the premier league this season and I have a separate worksheet with a drop down menu to load each team.
What I am struggling to do is to get a function that will obtain the data corresponding to each of the home fixtures and the away fixtures of the team selected in the drop down menu.
I have managed to get the first result but do not know how to get the 2nd occurrence, the 3rd etc.
My formula for the first is
=INDEX(Fixtures!$A$3:$R$380,MATCH($E$1,Fixtures!$C$3:$C$380, FALSE),5).
I've attached the spreadsheet. (Unfortunately the format of the excel sheet can't be changed)
Forum Example.xls
View 7 Replies
View Related
Jan 20, 2014
I am trying to find the 2nd occurrence in a range of text along 1 column and return the cell one to the left of that 2nd occurrence. I will also need to find the 3rd, 4th, 5th, etc occurences, down the column.
So far, I have a working formula to find the first occurrence and it looks like this:
=INDEX(INDIRECT(A2),MATCH("*SS*",INDEX(INDIRECT(A2),0,2),0),1)
How would I find the 2nd occurrence?
what if the formula looked like this?
how would I find it's 2nd occurrence?
{=INDEX(INDIRECT(A2),MATCH(TRUE,ISNUMBER(MATCH(INDEX(INDIRECT(A2),0,2),{"CF","LF","RF","OF"},0)),0),1)}
View 3 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
May 13, 2007
I have 2 spreadsheets with
Spreadsheet #1 All Vendor Transactions
Column D Column F Column V
Vendor Name Invoice Number Check Number
ABC 123 ?
Spreadsheet #2 Checks 1st QTR 2007
Column B Column D Column E
Vendor Name Invoice Number Check Number
ABC 123 99777
DEF 246 96504
XYZ 123 98654
I need to get Checks 1st QTR 2007!E1 Check number into All Vendor Transactions!V1
I could have done a VLOOKUP, but many of the values in Checks 1st QTR 2007!D1:59999 are duplicates as different vendors use the same invoice numbers. For various reasons,
I can't just use the Checks spreadsheets from each quarter.
I tried to use Index/match but kept getting my formula wrong.
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
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 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
Jul 1, 2014
I need to write a formula using Index Match that is looking at to criteria field. I've done this formula before with one criteria field but now I need 2 and the way I'm writing it isn't working.
=(INDEX('Agent Weekly DataSheet'!G:G,MATCH('Agent Weekly Overall Scores'!B8,IF('Agent Weekly DataSheet'!F:F='Agent Weekly Overall Scores'!G8,IF('Agent Weekly DataSheet'!B:B='Agent Weekly Overall Scores'!C8,'Agent Weekly DataSheet'!B:B),0))))
The bolded piece is what I can't figure out.
View 2 Replies
View Related
May 4, 2009
I have a couple of desired outcomes.
1. I want to find the “Close” price based upon “Date” and “Time” input (search criteria).
2. I want to know when (what time) a “Price” (input) falls between the “High” and “Low”, on a specified date.
DateTimeOpenHighLowCloseVolumeTimeDateTimeCloseTime2/24/200913:00138.21138.23138.18138.22410.022572/24/200913:00#N/ABUY2/24/200913:01138.23138.30138.18138.26680.02260SELL2/24/200913:02138.25138.28138.21138.28340.022632/24/200913:03138.27138.37138.27138.35680.022662/24/200913:04138.36138.41138.31138.33440.022692/24/200913:05138.34138.44138.32138.39330.022712/24/200913:06138.40138.45138.37138.41350.02274
View 9 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
Jun 8, 2014
what 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 1 Replies
View Related
Jun 9, 2014
Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:
EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1
Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.
I have attached a sample sheet for reference : Book1.xlsx‎
View 2 Replies
View Related
Jun 22, 2014
with a multiple criteria index match array!
I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!
I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)
View 6 Replies
View Related
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
View Related
Dec 3, 2013
In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.
In cell B12 i have an index match that I can not get to return a value.
Is Index Match the correct method of returning a result? Or have I just got the formula wrong?
View 1 Replies
View Related
Mar 4, 2014
I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.
I am currently using the formula
=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
View 2 Replies
View Related
Dec 29, 2009
hope the below example is clear to understand and makes sense .....
View 10 Replies
View Related
