I am trying to get my INDEX & MATCH formula to retreive data from my table.

This is what I can do so far: Jan-07Feb-07 100 12 250 45

=INDEX(table,MATCH(B13,balance),MATCH(C13, date))

But I am trying to get it to get another row to look up as well. I want it to look up the color then the 100 or 250, then the date. Jan-07Feb-07 red100 12 red250 45 blue100 78 blue250 1011

=INDEX(table,MATCH(B13,balance),MATCH(C13,date)) I think i need to insert another match code in the row section but cant seem to get it to work.

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)

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.

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.

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

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

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.

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â€Ž

We're selling language courses that have a different price depending on when a course is taken. We're trying to build a price quote application that needs to look up a certain school and course and price driven by a start date.

How can I build a lookup function that says: pick the price of school X and course Y when the start date falls between dd/mm/yyyy and dd/mm/yyyy?

I manage to build a look up function with MATCH and INDEX when the condition of start date is exactly matched but dont know how to instruct it to match a value between a start and end date.

Now, I would like to add the ability to perform this same action/concept, but using two different criteria.

So I want to keep the hierarchical listing of importance, but lookup/match within using more than one criteria.

Example: I have an order of superseding to apply to results of a search for Fruit. Great Good Fair Poor (so Good supersedes, Great; Fair supersedes, Good; etc.)

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?

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?

I'm having trouble using wildcards for text in index/match multiple criteria; I need to find the nearest site along a river (x) below a certain point (distance =21), each stretch of the river is labelled with the streches downstream (so stretch abc is upstream of ab).

My problem arises when the nearest point downstream is on the downtsream stretch of river. How do I use wild card to search for ab&"*" that will exclude abd.

River Distance Stretch

S1 x 10 a

[Code] ..........

'formula in cell E14 ={INDEX(A2:C11,MATCH(1,(A14=B2:B11)*(C2:C11

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.

I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.

Column 1 Lists the various factories Column 2 Lists the Machine Column 3 Lists the Part Column 4 Lists the result.

Results can either be "ok", "warning", "Alert" only

I need to Index & Match the Max Value for 3 separate columns labeled "Price". The columns are not adjacent and cannot be moved. I am able to get the Max for the 3 Price columns using:

=MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45).

However, I need the matching value in column D labeled "Int. SF". I can only do this for one column at a time so far using this formula

I am trying to understand how to use index and match in an array formula. Probably easiest to take a look at my example sheet. For some reason, the first result is working, but the others aren't.

[URL] .....

Alternatively, here is a screenshot.

Uploaded with ImageShack.us

The formula I have tried is: =INDEX($E$3:$E$11,MATCH(1,IF($A$3:$A$11=G3,IF($B$3:$B$11=H3, IF($C$3:$C$11=I3,IF($D$3:$D$11=MEDIAN(J3,K3,$D$3:$D$11),1,0) ,0),0),0),0))

I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.

There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).

Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.

Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.

I have a userform that collects production data from several production lines each running up to 4 shifts every day. This populates a table for performance related calculations to be applied.

Each day I must produce a report for each line and shift showing the performance stats I have calculated. the format of the report has been defined for me and I'm not allowed to change it at the moment.

I wrote a line of code to be applied to each cell I wanted to show the data in excel 2007 but this doesn't work in 2002 which is the version in our office and I'm hitting my head against a brick wall.

Essentially the report will either use Now() or get the user to enter a date, then I need to search for the unique reference that meets the date, the line and the shift and return several values in the report from the row in my "daily data" table.

Here is the code I have so far - it returns a "VALUE" error...

I have one sheet which summarises the status of documents in another sheet. I'm using INDEX and MATCH to return the correct Document title and info. But I need to offset/countif/INDEX MATCH again or something to get to reference the latest revision status of the document.

In this L98 is the criteria I'm in theory doing a vlookup against the values in column $K$27:$K$82, and N98 is the theoretical hlookup against row $K$15:$AV$15, bringing back the intersection point from row $K$46:$AV$46