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

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
- Find nth occurrence with multiple criteria
- Using MATCH-INDEX to list occuring dates of values in a data table
- Match/Index or VLOOKUP in VBA?
- Match / index to lookup any data greater than 0
- Lookup Nth Occurrence Of Date & Return Adjacent Cell
- Need help with MATCH, INDEX or VLOOKUP
- "Nth" occurrence...
- VB Macro for Match Index
- Challenge! - match/index/lookup with multiple criteria
- Match Index
- Match Each Numeric occurrence and Return Individual Rows of Data
- Four criteria MATCH INDEX lookup of date between matching two text
- Lookup/match/index
- Return multiple column data from match index
- Need to Extract data from a row using Lookup, Offset, Match, Index(?).
- Lookup/match/index
- How to incorporate IF and VLOOKUP formula to a Match Index Formula?
- Match Index Lookup
- Match Index or Vlookup formula question
- Match Each Numeric occurrence and Return Individual Rows of Data
- Match& Index and Sumif
- Match/Index Returning #N/A
- Using Match/Index on 2d Array

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: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.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 FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

"~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.

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.

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

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 FunctionIf 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

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!

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"?

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

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?

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

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

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

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 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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?

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

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?

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

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

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 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.

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