Match Index Nth Occurrence

Hi There,

I'm a relative newcomer to Excel and am having some problems with lookups.

I have tried using the 'arbitrary lookup' function as listed here but can't seem to get it to work. http://www.cpearson.com/excel/TablesAndLookups.aspx

Basically, 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)

Any suggestions would be greatly appreciated.

I've attached the spreadsheet. (Unfortunately the format of the excel sheet can't be changed)

Thanks in advance for any advice. Hopefully, if I learn some more about Excel, I can contribute to the forum myself


Find Nth Occurrence in Excel As you may already know, we can use VLOOKUP, or INDEX/MATCH to locate the first occurrence of a specified value in a list, or table of data. However, Excel has no ready made formula that allows us to locate say the second, or third occurrence etc of a specified value

Hi All,

I am trying to write a VBA function that combines two elements that I found on the internet:Finding the nth occurrence of a value in a range (see http://www.ozgrid.com/Excel/find-nth.htm), andUsing concatenate (or &) to combine multiple criteria (see method 2 of http://support.microsoft.com/kb/214142).So, ideally I would like to end up with a function call that looks like this:
=findNthOccurrence(1, B6&E6, 'Data'!M2:M1225&'Data'!B2:B1225, 'Data'!J2:J1225)

This is what I have so far:

	VB:
	
 Range) 
     
    Dim nCount As Integer, iValue As Integer, iFound As Integer 
    Dim rFound As Range, r As Range 
     
    iFound = 0 
    iValue = 1 
    For nCount = 1 To n 
        Set r = Range(lookupVector.Cells(iValue), lookupVector.Cells(lookupVector.Rows.Count)) 
        iValue = Application.WorksheetFunction.Match(lookupValue, r, 0) + 1 
        iFound = iFound + iValue - 1 
    Next nCount 
     
    If resultVector Is Nothing Then 
        findNthOccurrence = lookupVector(iFound) 
    Else 
        findNthOccurrence = resultVector.Cells(iFound) 
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It works for simple arguments (like =findNthOccurrence(1, E6, 'Data'!B2:B1225, 'Data'!J2:J1225)), but it doesn't work with the & in the arguments. Any suggestions on how to do it? Thanks.

I'm parsing a large table into a report and could use some help. In a two page Excel workbook I have the following tabs:

"~data tab~"
date | type
6/21/2011 | Blue Volvo
6/21/2011 | Red Citroen
6/23/2011 | Yellow Mazda
6/23/2011 | Green Ford
6/23/2011 | Silver Toyota
6/23/2011 | White Landcruiser
6/28/2011 | Blue Volvo
6/28/2011 | Red Citroen
6/28/2011 | Yellow Mazda
6/28/2011 | Green Ford
7/2/2011 | White Landcruiser
7/2/2011 | Silver Toyota
7/2/2011 | Green Ford
7/2/2011 | Yellow Mazda
7/3/2011 | White Landcruiser
7/3/2011 | Blue Volvo
7/12/2011 | White Landcruiser
7/30/2011 | Red Citroen

"~report tab~"
type | date
White Landcruiser | 6/23/2011
<blank> | 7/2/2011
<blank> | 7/3/2011
<blank> | 7/12/2011
<blank> | <blank>
<blank> | <blank>
<blank> | <blank>
<blank> | <blank>
<blank> | <blank>
<blank> | <blank>
Green Ford | 6/23/2011
<blank> | 6/28/2011
<blank> | 7/2/2011
<blank> | <blank>

Currently B2 (and B12 (w/MATCH target modified)) on the report tab have this entered,
"'=INDEX(data!$A:$A,MATCH(A$2,data!$B:$B,0),1)", with A2 (and A12) hardcoded as "White Landcruiser", (and A12 as "Green Ford").

Clearly the function that I am using does not provide all of the dates shown: it only provides a repeat of the initial date in following rows.

Any suggestions regarding how I can list the date of occurrences of "type" from the data tab? Thanks in advance for advice and suggestions that use MATCH-INDEX to do this.

I want to use Match/Index or VLOOKUP in VBA to locate a cell in another worksheet and perform a calculation.

Sequence of Events:

-User will select a Component from a drop-down (Cell B5) and hit an "Overhaul Completed" button
-The button will look up the selected Component in another sheet where all components (A column) are listed with lots of data about them
-The corresponding cell for that component will be selected in the "Hours till Next Overhaul" column (C column).
-For this example, lets say that the cell selected is C5
-The formula for C5 should currently be B5-(PME!G1), where the value imported from PME! is the current engine hour value and it changes every month
- The formula should now change to B5-(PME!G1)-(PME!G1). But the second PME!G1 is going to a static number that will never change. So if the hours are at 14474 this month, the formula should be B5-(PME!G1)-14474.

This is what I have so far:
Dim rngRequested As Range
Set rngRequested = Sheets("Sheet10").Range("B5")
.VLookup(rngRequested.Value, Sheets("Sheet7").Range("A3:C21"), 3, False).Select

The select and vlookup functions are probably used incorrectly here.. So once I have that fixed, I can move onto the calculation part...

The reason why I might seem to be going a bit crazy on the automation is because I want to exploit the full capabilities of excel/vba to make the workbook idiot proof. There will be many operators using this book and our goal is to have them do a little as possible, while getting all sorts of valuable data.

Hey all,

Thanks for being so helpful on these forums, I am learning a lot. I have a question using match / index or vlookup to pull data from one worksheet to another. Take a look at the attached example it will hopefully make my explanation a bit more clear.

I have an input page where coworkers will input weights into column C as to what data they want to consider. What I want is for only the Metrics that are marked with a value >0 to be reflected in the output page. I figured out how to do this with the first metric marked with a value greater than 0, but I am struggling to figure out the remainder.

A few notes to keep in mind, I can't sort the data or use VBA (my boss lives in the dark ages and doesn't "trust them"). Also, I have a ton of data and metrics so I want something that is computationally not too intense. For that reason I am trying my best to avoid arrays or the indirect function.

Any help would be much appreciated.

Example.xlsx

Hello everyone, first time posting.

I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:


	VB:
	
, _ 
    occurrence As Long, offset_row As Long, offset_col As Long) 
     
    Dim lCount As Long 
    Dim rFound As Range 
     
    Set rFound = range_look.Cells(1, 1) 
    For lCount = 1 To occurrence 
        Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole) 
    Next lCount 
    Nth_Occurrence = rFound.Offset(offset_row, offset_col) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The excel formula I am using is as follows:
=Nth_Occurrence(AF2:AF622,AE2,2,0,1)

Where AE2 is the date I am looking up.

My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.

Thanks for any help

I'm not sure if there is a way to do this - I've been trying to figure it out for 2 days but can't seem to get the MATCH/INDEX or VLOOKUP features to work correctly.

This is my challenge. I have two spreadsheets. The first includes customers with a single customer number in a column. On the second spreadsheet some customers have multiple customer numbers separated by commas. What I need to do is a search on the customer number in the first spreadsheet to see if it matches any of the customers in the second spreadsheet, if there is a match then return the customer service support name.

For example:

Spreadsheet 1

Column 1
Customer No = 1234

Column 2
Customer Name = ABC company

Column 3
Service Rep = (this is what I'm searching in the second spreadsheet, based on the second spreadsheet the answer would be John Doe)
**********************
Spreadsheet 2

Column 1
Customer No = 4534, 1234, 5643, 6546

Column 2
Customer Name = ABC Apples Corporation

Column 3
Service Rep = John Doe
**********************

I can't search on customer name because it may be different in the two spreadsheets so the only good value for me is the customer number but some customers have multiple numbers.

Any ideas/recommendations?

Thanks!

This is a 2 part question.

1) Is there a formula that can be used to find the "nth" occurrence of a character in a string?

For example, how could you determine that the 3rd occurrence of "s" in the word "suggestions" is the 11th character?

2) Is there a function/formula that would return "3" for the number of times "s" appears in "suggestions"?

HI All,
I need your help to write a Macro which will match a string in all the
sheets and Index the corresponding values next to it.

Eg i have 4 sheets in a Workbook. in the sheet4 i have values

A1: 8001
A2:8002
A3:8003
....
....

and sheet1,sheet2,sheet3 as values like this

A1:7001 B1: Sold
A2:8002 B2:Not sold
A3:6005 B3: Not sold
....
....
....

Now in Sheet4, I want to search for values in range A:A(sheet4) in all the
other sheets in the workbook and Index the corresponding values in Range B:B.

I know how to use Match Index function, but i want to write a code what will
match the value in all the sheets in the workbook and retrieve the
corresponding value.

In addition to that.. I would also like to know the code snippet if i want
to run this code to retrieve values from other excel file and not from the
other sheets of the same file.

Hope I am able to express my query. Any help will be appreicated

I have a roster that I am trying to automate. I would like to be able to
place the date in a cell and in another cell return the correct crew for that
date. This is complicated by the fact we have two shifts per day, 3 crews
that alternate between these shifts and each date corresponds with a
different crew roster. This may explain it a bit better:

each line corresponds to a month, but the dates in column one do not start
with the 1st day of the month - dates correlate to days of the week
(perpetual calendar)

week crew mon tue wed
1 a ngt day day
b day ngt off (blank cell)
c off off ngt

2 a d off n
b n n off
c off d d

ditto for week 3

mon tues wed thurs
week 1 10 Jun 11 Jun 12 Jun 13 Jun
week 2 8 Jul 9 Jul 10 Jul 1 Jul
week 3 5 Aug 6 Aug 7 Aug 8 Aug

I would like to select a date in another worksheet, and in another cell have
the date on the above example recognised and return in this other cell the
corresponding crew and shift that they are on. eg. for 9 July the answer I
need is "b crew ngt shift"

Can anyone help please - I've tried lots of combos of match, index and
lookup and can't seem to get it to work?

just wanna ask about the use of match and index. in this scenario, i
want the cell to perform a match index and if there is a match, it will
return the value, if thee is no match, carry out another formula. This
is the scenario:

A B
01 x123
02 y456
01 x123

in this e.g, the formula use in A2
=INDEX(A$2:A2,MATCH(B2,B$1:b2,FALSE))

but if there is no match, the cell will return a NA but now i would
like the cell to perform another formula if there is no match . Can it
be done using IF function?

something like if match, do_match(index),
if_no_match_carryout_other_formula?

hope that i am clear..

pls advise

--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=478698

Hi All,

Match Each Numeric occurrence of ONE Criterion and Return Individual Rows of
Data (Multiple Columns):
the Numeric Criterion may only appear ONCE in a Row, if at all.

I have a Dynamic List called "History" that spans 7 Columns and many Rows on
Sheet1:
1st Column houses ID - numeric value, formatted General
2nd Column houses Date - numeric value, formatted 26/9/2005
3rd-7th Five Columns house numeric values: single/double-digit, formatted
General.

I would like a Formula to search every Row of the 3rd-7th Column of the
Dynamic List "History" for each occurrence of a particular single-digit or
double-digit numeric criterion by using an Input Cell for flexibility (so I
may specify whatever numeric criterion to match within the List).

Each matched occurrence of the specified numeric criterion should Return the
following data from its Row within the Dynamic List "History": ID, Date and
actual Numeric Value.

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

I got stuck after 2 criterias, can anyone help! I need a formula that returns
1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula
shall return 1 if the assembly workcell is occupied on the dates in
Sheet2!A2:A1000, and 0 if not.

For the formula in Sheet2!B2:
Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup
range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000
Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000.
Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000

I have read several of the MATCH INDEX threads on this forum, but I cant get
tit to work with 4 criterias combined with the "between dates". Dates are
formated as dd.mm.yyyy. Text is formated as "General".

Sheet1: (Letters P, X, Y, AI are col. headings)
P X Y AI
Status code Cell name Start date Finish date
Assembly Cell1 01.10.2008 31.10.2008

Sheet2: (Results in B2 etc.)
A B C D
Running dates Cell1 Cell2 Cell3
30.09.2008 0 0 0
01.10.2008 1 0 0
02.10.2008 1 0 0

--
Thanks
John_J

I have two worksheets...I need to match serveral criteria...I have tried
match, index, vlookup...to no avail.

I have to match the surgeon with the correct day and time his/her room is
available to the time in which they preformed the surgery.

so, surgeon block (time they reserve) with the time they do surgery (a
seperate worksheet) contianing surgeon, date, time of case.

anyone know what formula or function I can use?

Thanks os much for the time.
--
Jules

I am completely stumped on something that seems so simple. I have searched and searched and have yet to find code to solve this.

I have two worksheets (build and itrw01).
I am using match index to match buildsheet, col a to itw01sheet, col d, then copy the value from itw01sheet, col e to buildsheet col d.

the code works for this, but I really want to copy columns e,g,h,i to the build worksheet. This is where I fail. Any ideas?


	VB:
	
 buildsheet() 
    Dim vmatch2 As Variant 
    Dim rngcell As Range 
    Dim wssource As Worksheet 
    Set wssource = Sheets("itrw01") 
    Dim buildsheet As Worksheet 
    Set buildsheet = Sheets("Build") 
    Dim lastrowindex As Long 
    Dim lastrowlu As Long 
     
    With buildsheet 
         
        lastrowindex = Range("a65536").End(xlUp).Row 
         'lastrowlu = Range("e65536").End(xlUp).Row
         
        For Each rngcell In buildsheet.Range("d11:d" & lastrowindex) 
            vmatch2 = Application.Match(buildsheet.Cells(rngcell.Row, "a"), wssource.Range("d11:d" & lastrowindex), 0) 
            If IsNumeric(vmatch2) Then 
                rngcell.Value = Application.Index(wssource.Range("e11:e" & lastrowindex), vmatch2) 
                 'rngcell.Value = Application.Index(buildsheet.Range("a11:a" & lastrowindex), vmatch2)
                 'MsgBox rngcell.Address
            Else 
                rngcell.Value = "no match" 
            End If 
        Next rngcell 
        Set wssource = Nothing 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Please help.

My data looks like this:
A2:A6 = Account 1,2,3,4,5.
B1:F1 = Monday 1st Jan to Friday 5th Jan.
In between this data are random balances of $2000, $0, or nothing (empty cell). eg. Account 1 has balances of the following for its week:
-,2000,0,-,2000 (for Monday-Friday).

My request: I need to report the accounts that have been imbalanced for 3 days or more.
In more detail, how can I work out per account (1.) the current balance; (2.) the dates the current balance appeared, and (3.) the date that the last 0 appeared? I have used all manner of offset, match, index, lookup functions to work this out, but little luck so far.
I have worked out the current balance with this formula:
=LOOKUP(MAX(B2:F2)+1,B2:F2)The dates though, are proving difficult.
Any tips?

I have two worksheets...I need to match serveral criteria...I have tried
match, index, vlookup...to no avail.

I have to match the surgeon with the correct day and time his/her room is
available to the time in which they preformed the surgery.

so, surgeon block (time they reserve) with the time they do surgery (a
seperate worksheet) contianing surgeon, date, time of case.

anyone know what formula or function I can use?

Thanks os much for the time.
--
Jules

I've a Match Index formula:

FORMULA = INDEX(D:D,MATCH(1,INDEX((F2=E:E)*(D2=B:B),0),0))

And I want to incorporate this condition

If value of (vlookup(A2, D:F, 3, FALSE) is GREATER than zero(0) do FORMULA

If EQUAL or LESS than zero(0) go to next row then if EQUAL or LESS than zero again go to next row

If finally in the last row it gives GREATER than zero then do the FORMULA

Is this possible?

Hi - have done this in the past for unfortunately forget

I have a list of days in number format say from 365 in my table. I have set
up a list of then 12 mths so in excel I have in cell
A1 = 365, B1= 12mths
A2 = 335, B2 = 11mths
etc

I want to lookup a value in my table and match it to my list so that for
example if the value is 360, the lookup would go though my list and realise
that 360 is less than 365 and skip to next value and then look to cell A2 as
as 360 is above the value in A1 it returns the value in B2.

I think it is quite a simple match / index type lookup (where you have to
have you values in a list with the highest value first in the list)

All advice much appreciated thanks
--
Kevin

Hi all

I have an issue with a formula in excel, I can't seem to figure out which one to use, I seem to come close but can't quite figure it out.

In the attached file I've tried to replicate my problem but on a smaller scale.

I will be receiving data each month that looks like 'sheet2' Or I can also receive it in the format shown in 'sheet3' if that's easier to work with.

I want to get it so that it is in the format shown in sheet1, so I want to look up the respondent number row, look up the code column and place a '1' if that respondent matches the code. I can't use a pivot table as in 'sheet1' I will also have some sample information and there won't be the same codes every month.

I thought I had the perfect formula "=IF(VLOOKUP(A2,Sheet2!A:I,2,FALSE)=Sheet1!B1,"1",IF(VLOOKUP(A2,Sheet2!A:I,3,FALSE)=Sheet1!B1,"1"... ... etc etc" but it wouldn't let me nest more than 7 in a row or something like that! I also tried a match index but can't quite get my head around it hence why I'm asking for help.

If anyone can find a way I'd be really grateful! Sorry if I haven't explained myself clearly but if you have any questions just ask.

Thanks
Ian

Hi All,

Match Each Numeric occurrence of ONE Criterion and Return Individual Rows of
Data (Multiple Columns):
the Numeric Criterion may only appear ONCE in a Row, if at all.

I have a Dynamic List called "History" that spans 7 Columns and many Rows on
Sheet1:
1st Column houses ID - numeric value, formatted General
2nd Column houses Date - numeric value, formatted 26/9/2005
3rd-7th Five Columns house numeric values: single/double-digit, formatted
General.

I would like a Formula to search every Row of the 3rd-7th Column of the
Dynamic List "History" for each occurrence of a particular single-digit or
double-digit numeric criterion by using an Input Cell for flexibility (so I
may specify whatever numeric criterion to match within the List).

Each matched occurrence of the specified numeric criterion should Return the
following data from its Row within the Dynamic List "History": ID, Date and
actual Numeric Value.

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

Hi
I have a spreadsheet with a worksheet that has all the budget plans and
another worksheet with summary YTD. What I would like to do is on my
summary sheet pull through the cumulative plan to date based on the
month entered in cell G5.
So far I've managed to identify which columns need to be summed based
on what is entered in G5, by using an IF statement. I then have a sumif
(in column D)which would sum all the values for the valid month in G5.
The problem I am having is now combing this with a lookup of some sort.

I think I need to use a match & index and sumif... but not sure how to
contruct this.
There may be a simpler way. Any help is appreciated.
Below is what I hope to achieve

User enters month number into G5
Column D is then updated...based on indicator in Column C, it shows the
cumulative YTD budget.

Hi,

Been searching previous post for an answer to my question, but I got
nothing.

Here's my problem:

I have two tables in two worksheets. Fist table consists of sizes,
broken down by their different types, i.e.

Fractional Letter WireGauge Metric

Then I have another table that consists of product categories. The
categories are determined by the product type and size, i.e.

Fractional Letter WireGauge Metric
Product type Cat1 Cat2 Cat3 Cat4

In a third worksheet I have a 32,000 row list of the products and their
different sizing. I need to categorize these products. The formula
that I thought would do it is

=Index(Table,MATCH(product,ProductList,0),MATCH(Index(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGauge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1)

I thought that by setting the value if false to 0, I would get the
value that corresponds to the correct size type, but instead it returns
#N/A. I can't think of anything that could fix it.

Hope I explained everything well enough. Any help would be
appreciated.

-Sern

I have an array of data (HourData) with 20 rows and 6 columns as seen below.
I want to find the row where the value in the 6th column is equal to or
greater than a value (X = 2700). This should return the row 12:

8 D E 491 21 2918

I was simply going to use a Match/Index to do this on the 6 column of the
array:

Answer = Application.WorksheetFunction.Match(X, Application.Index(HourData,
0, 6), -1)

As I know that it may not an exact match, I have used the -1 at the end of
the match to find the smallest value that is greater than or equal to X.

However I am getting an error message saying "unable to get Match property"

What have I missed here?

Thanks
EM

***************************************************
7 W D 437 0 437
5 N E 73 0 510
7 W D 67 0 577
2 D D 87 5 664
8 D E 99 6 763
5 N E 492 10 1255
10 D C 309 11 1564
3 C B 87 13 1651
2 D D 313 13 1964
6 U C 211 14 2175
9 D E 252 18 2427
8 D E 491 21 2918
4 U E 82 22 3000
1 C E 241 22 3241
4 U E 57 23 3298
10 D C 69 30 3367
3 C B 207 31 3574
1 C E 61 33 3635
6 U C 81 34 3716
9 D E 84 49 3800