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
ADVERTISEMENT
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
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
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
Dec 3, 2013
In the attached I have an index/match formula in B15 that I cant get to return any results not even a N/A#
Quick Quote Tool.xlsx
View 2 Replies
View Related
Mar 2, 2014
I am trying to sum the data in "details sheet" column H into "monthly sheet" based on the period and type match.
I am not sure where I am going wrong as the column references returned values that they should. However, the values in Col H from details sheet are not being summed up based on the criteria into monthly sheet.
I am getting zero amounts.
View 5 Replies
View Related
Dec 15, 2009
In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?
View 4 Replies
View Related
Mar 1, 2014
making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results
View 6 Replies
View Related
Sep 29, 2012
I am currently using the Match and index functions to search for specific criteria and return specific data based on that criteria. The problem is the formula is returning the first value at the beginning of the month rather then the end of the month value that I am asking for. I have my criteria set to zero so it should be a perfect match. with the live worksheet I have several tabs for different years. I want to take the end of month balance on each of those year tabs for each month in each fiscal year and add them together. My formula only has the one month because I am unable to get it to work much less adding in all of the years information. I have the employees choosing EOM for the last transaction posted during that month and that calculates the month number in the field next to it. I was thinking I could use the match to look for a specific month in that column and return the balance adjacent to it at that point.
View 7 Replies
View Related
Nov 20, 2012
I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.
I need a formula that will find the column that meets two conditions:
1) Today's date
2) A specific time (referenced from a cell on the main sheet)
It must then pull the data from the cell in that column that is in the row marked "Total".
Here is an example of the data set from the web query:
Excel 2010BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATA
UAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT3
Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-
Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-
Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-
Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-
Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-
[Code]......
I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:
=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!$D$3:$JS$3,1)))
This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.
=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(TODAY()&O3,'Rodeo Data'!$D$3:$JS$3&'Rodeo Data'!$D$4:$JS$4,))
This one returns the standard "#N/A" error message.
I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.
The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.
It may also be worth noting that the number of columns and rows in the data set is dynamic.
View 3 Replies
View Related
Mar 21, 2014
I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.
Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.
Attached is my example spreadsheet of my chart. Book1.xlsx
I have shown what I need, what I have tried and a blank space to show where I need it to go.
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
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
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
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
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
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
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
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
Feb 18, 2014
I know I can do this as I have already succeeded once but since moving a few things on my spread sheet the formula has gone wrong and I can't correct it.
I have one sheet with many different lines on and another I would like to select certain rows with the same reference number in column A. Below is the formula I am using
=IF(ISERROR(INDEX(Lines!$A$2:$J$200,SMALL(IF(Quote!$AL$4=Lines!$A$2:$A$200,ROW(Lines!$A$2:$A$200)-
MIN(ROW(Lines!$A$2:$A$200))+1,""),ROW(Lines!$A$2:$A$200)),COLUMN(C1))),"",
INDEX(Lines!$A$2:$J$200,SMALL(IF(Quote!$AL$4=Lines!$A$2:$A$200,ROW(Lines!$A$2:$A$200)-
MIN(ROW(Lines!$A$2:$A$200))+1,""),ROW(Lines!$A$2:$A$200)),COLUMN(C1)))
what my error is as it is returning the same value for me each time.
View 4 Replies
View Related
Oct 19, 2007
I have a formula I need to modify.
In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c
The idea with the orginal formula was to look up the item and return the date
=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))
Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?
I trying to return the most recent date and price for a item.
I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
View 6 Replies
View Related
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
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
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
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
Apr 20, 2013
I am using the following formula:
=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
Using Excel 2003
View 9 Replies
View Related
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
Apr 10, 2014
Not sure where to start with this one, an IF statement or vlookup? Basically I have 3 columns A, B, C (original sheet) and A, B, C in my working sheet. A contains car registrations, B contains a date and C contains a weight. I need to return C from my original sheet into my working sheet only when A and B BOTH match from my working sheet (there are scenarios where the same car registration has a different date next to it). So I want to return the weight only when the car registration and the date match in a specific instance from my working sheet.
View 6 Replies
View Related
Jan 27, 2014
I have two tabs (In-Transit) & (AC) column A in both sheet have a "Concatenate" that i copied and pasted and values only and also switch the format to "Text" on both tabs. I do a vlookup
[Code] ......
and it returns the results i want....But then i was thinking due to the volume of data what if the value in column A is found twice on tab "AC".. i wouldn't know with a "Vlookup".
So trying to build a Index/Match formula
[Code] ....
I also returns some good results, but i also get some back as #REF. I also get #NA and both of the formulas and those are valid no matches...
View 2 Replies
View Related
Dec 8, 2009
I'm using vlookup to get a price for a unit of measure and it passes the value of the lesser not the greater. Is there anyway to get vlookup to pick the closest match as the greater value not the lesser one?
For example:
1000 units costs $8,000
4000 units cost $23,000
12000 units cost $68,000
If someone wants to buy 2500 units, I'd like the vlookup to pass the price based on 4000 units, but it passes 8,000 the value on 1000 units.
Can I use vlookup to give me the result of the greater unit not the lesser?
View 4 Replies
View Related
