Match Index Nth Occurrence?

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


ADVERTISEMENT

Returning 2nd Occurrence Using INDEX And MATCH

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

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 View Related

Index & Match Multiple Items/Criteria: Finding The Nth Occurrence

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

Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

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

VBA Find Exact Match And Nth Occurrence

May 30, 2012

This section of code I am working on requires me to find an exact text (I tried lookat:=xlWhole, but I can't get it to work right). The problem being is when it searches for Q1, Q10 is an acceptable answer ans so on. I need to get the 4th occurrence, but my research into Nth occurrence stuff is confusing.

The search is in a single column.

With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("PBA Crosstabs").Columns(1)
Set c = .Find(rCell, LookIn:=xlValues, lookat:=xlWhole)
Set c = .Find(rCell, After:=c, lookat:=xlWhole)
Set c = .Find(rCell, After:=c, lookat:=xlWhole)
Set c = .Find(rCell, After:=c, lookat:=xlWhole)
End With

View 9 Replies View Related

VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??

Oct 8, 2009

I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)

View 2 Replies View Related

Counting Number Of Times INDEX / MATCH Finds More Than 1 Match?

May 23, 2014

We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.

sampleexcelhelp.xlsx

The columns that need to be filled are shaded in dark pink.

View 5 Replies View Related

Index And / Or Match Formula With Multiple Match Required To Return A Value

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

VLOOKUP / INDEX And MATCH To Return All Values That Match

Feb 4, 2014

I have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.

One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.

View 3 Replies View Related

Using Index / Match Function For Two Column Match Values

Aug 28, 2012

Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.

2011
2011
2011
2012
2012
2012

[code].....

View 5 Replies View Related

INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies View Related

Lookup/Index-Match-Match Using Segments

Jun 10, 2009

Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.

View 10 Replies View Related

Match Upper Closest Value Using Index-Match

Jan 15, 2013

I couldn't resolve the formula of Index-Match for getting the values that matches the following criteria

1) Match the "Category" of Table 1 to Table 2

2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.

Once the above conditions are met, Take the Result from Table 2

Table 1 is for Input and Table 2 is for Database.

Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didn't work out.

Have a look at the file attached : IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx

View 3 Replies View Related

Adding A Third Match To An Index Match Formula

Oct 6, 2009

I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:

View 3 Replies View Related

Sum If W/ Index Match On Vertical And Horizontal Match

Sep 27, 2007

I am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):

{=IF(ISBLANK(G$9),"",SUM(IF(INDEX('Journal'!$A$20:$Z$1020,MATCH("Prior",'Journal'!$F$20:$F$1020,0),MATCH(G$9,'Journal'!$H$19:$Z$19,0)),"")))}

View 9 Replies View Related

Hlookup And Match Or Index And Match?

Apr 6, 2009

Example:......

On my worksheet 1, I have two dropdown boxes: B7 (that gives the building name) and B11 which gives the Type of Commission (Renewal, Expansion, New). B 20 is supposed to automatically select the percentage based upon what B7 and B11 select. E.g. B7 = House, B11 = Expansion then B20 should be 3.

This is my formula.
=HLOOKUP(B7,'Building Details'!$A$2:$R$24,MATCH(B11,'Building
Details'!$A$2:$R$2,0))

View 3 Replies View Related

Value X - Match Occurrence In Row A With Parallel Cell In Row B / Return Row B Cell Value

Dec 21, 2013

I have a value in $ED$1. This is Value X.

Row A = $DO$7:$DV$7
Row B = $DO$10:$DV$10

[Formula location = $EE$1]

Where Value X occurs in Row A, l want to return the parallel cell in Row B.

Both rows are sorted in numerical order. There is a slight complication though: Row A might contain multiple occurrences of Value X. If Value X is repeated several times in Row A, I want to find the left-most occurrence of Value X in Row A, i.e. the one closer to Column DO.

View 5 Replies View Related

Calculate Only Last Occurrence Or Occurrence Before Specified Number

Dec 24, 2013

I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue

At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.

The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.

Result is this in the CDR

FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00

The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.

How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?

View 2 Replies View Related

IF INDEX And MATCH Together

Jan 8, 2014

I am using sheet 1 to pull data from sheet 2 using this formula

=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0)).

This on its own works to pull the data. However the data is repeated for each 'set' of data ie each group of repeating names, as I drag down. I just want the data to appear once on the first lie of each group so I have added an if to the formula

=IF(C2=C1,"No Match",

My complete formula is now

=IF(C2=C1,"No Match",=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0))

View 2 Replies View Related

Various Index Match

May 3, 2009

On the attached workbook, on the "Progress Summary" page, cell C30 has a formula which should list the names of pupils who made 0 progress (as referenced by the Raw Data page, column R [progress] and column A [name].)

View 3 Replies View Related

Index- Match

Dec 3, 2009

I have never used the aforementioned functions before, but i assume thats what i need to do here....

In the attached table, what formula do i use to return the value '8' i.e. whats in column "Epsilon" and row "Three"

If you could let me have the generic formula i need as well, that would be good.

View 11 Replies View Related

Index,match = #n/a

Feb 3, 2010

I can't figure out why the N/As are coming. In G2 I have =INDEX(Area_Codes!$D:$D,MATCH(F2,INDEX(Area_Codes!$B:$B,0),0)). If I replace F2 with 407, I get FL which is right. But why do I get N/A??

View 4 Replies View Related

Match The INDEX

Feb 18, 2010

L26 returns incorrect information. Input parameters are F29 and F30. In its current state, it returns the value “b”, where I think it should be returning “1”, i.e the intersection point for “3Ph” & “Single-core 70°C thermoplastic non-arm Cu Table 4D1”

View 6 Replies View Related

Index And Match The Value

Mar 5, 2009

If the value on sheet2 columnA ,matches value sheet1, column AC
then corresponding row value sheet2,column E be put in corresponding row for matched value ,sheet1,column Z.

View 6 Replies View Related

Index/match Within Certain Value

Mar 30, 2009

I have the following array formula that matches against a table with 3 criteria (one specfically is a time), it works ok until the time criteria is not of the value searched for, it may be +/- 30 minutes out, is it possible for the formula to index/match to the actual value or to find the nearest match possible, its in column B.

View 3 Replies View Related

Index Match Getting #N/A

Apr 27, 2009

insert =IF(ISNA into the following formula? =INDEX(Data!$AE$2:$AE$10,MATCH(1,(Data!$AI$2:$AI$10=$A$11)*(Data!$AD$2:$AD$10=Sheet1!$A$6),0))

View 3 Replies View Related

Index, Match For VBA

Dec 17, 2006

I have the following code from my UserForm that works fine for my Summary sheet.

Private Sub TextBox2_AfterUpdate()
Application.ScreenUpdating = False
'//Get the Truck # to act on
Dim varInput As Variant
varInput = TextBox2.Value
'//Exit if no input
If varInput = "" Then
Exit Sub
End If

What I am trying to figure out is to do a Index,Match or what ever it takes to populate the Truck sheet (250+) with info from the user form.

TextBox1 is the date. TextBox2 is the truck number, 3 is the Milage, and 4 is the Cost.

All in relation to the following sheet for the truck: ...

View 9 Replies View Related

Index & Match

Mar 10, 2008

I have the below formula which works fine when I only want it to lookup one sheet but I am now trying to get it to lookup 2 sheets.

I have tried numerous variations but still no luck. Plus I am not sure that with the formula that I am using it is even possible.

HTML =INDEX('2008'!$B$2:$J$1697;MATCH($B6;'2008'!$B$2:$B$1697;0);2)+INDEX('2007'!$B$2:$J$1697;MATCH($B6;'2007'!$B$2:$B$1697;0);2)

View 9 Replies View Related

Index, Match Etc

Jul 5, 2008

I am having trouble finding the right formula to use to get the data I want. Let me outline it below:

Worksheet 1 contains raw data:
Column B : Date/Time i.e 8/1/2006 9:31am in one row, 8/1/2006 9:32am in the next row etc..
Column C: Temp i.e 33.5
Column D: Hum
etc...

Worksheet 2 is a summary worksheet and contains:
Column A : Date i.e 8/1/2006
Column B: Time i.e 9:31am
Column C: Max temp i.e 35.8 (calculated via array formula)

I wish to use a formula (which I can copy down the worksheet so it needs to apply to the whole column of the reference worksheet) to find the time at which the max temp occurred on each particular day. I don't want to use individual references for each day as it will take too long!

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved