# Index With Multiple Match

Mar 28, 2012
I tried searching some other Index/Match threads but figured I did not want to confuse the other original posters. I can index/match on a single set of criteria but what I am trying to do it add an additional match criteria into the equation and that is the last "MATCH" in my formula pasted below (MATCH($B3&"",O1:S1,0).

1)I need to match the value in Column A to Column N

2)Then for that combination, I need to match the combination value in Column B which ranges across Column O thru Column S

a.Ex. Cell A3

i.0001 matches to 0001 and B3 #50000 pulls in the value from Q3 (which would be 2)

Formula in J3

=INDEX($N:$S,MATCH($A3,$N:$N,),MATCH($A3,$N:$N,),MATCH($B3&"",O1:S1,0))

View 4 Replies
ADVERTISEMENT
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
Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies
View Related
Dec 2, 2013

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?

View 3 Replies
View Related
Mar 7, 2013

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)

but it does not seem to work. It returns #VALUE

View 1 Replies
View Related
Jan 10, 2014

To summarize - Columns B through E are current MLB Players who I am trying to compare with the data in Columns G though J. My goal is to both use index and match to find the following in Column L: A comparable player name using the criteria of having an exact match for both position and age, but the closest approximation match between Columns E and J.

I have deleted a significant amount of player data to be able to upload the document on this site.

I am trying to find a way to combined exact and approximate matches in one single formula, but have been unsuccessful thus far.

View 10 Replies
View Related
May 23, 2014

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.

View 5 Replies
View Related
Jun 8, 2014

i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?

e.g. worksheet 1 has all the data

A B C

email ID date

worksheet 2 has a list of specific email addresses i'm looking for info on

A B

email date

My formula in column B of worksheet 2 is along the lines of this:

index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))

basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.

The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.

View 6 Replies
View Related
Oct 7, 2013

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.

indexmatch.xlsxâ€Ž

View 3 Replies
View Related
Jan 16, 2012

I am trying to get a formula to work that looks up a vendor, and then checks the quantity of their order (less than 99999999, less 500, less than 200, and less than 50). I get either a #NAME or #N/A when I try to use what I have written. I tried to include an =index(array,AND(Match(row 1,exact match),Match(row 1 less than quantity),Match(row 1,greater than quantity)),Match(rate,exact match)) returning the variable rate. I have had no luck using vlookup and pivot tables.

View 6 Replies
View Related
Aug 1, 2012

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

[Code] ........

View 7 Replies
View Related
Jul 22, 2013

Basically, I want a code that does a simple index-match function for a column - But for all the #N/A's that come up in that column, I'd like it go to another INDEX-MATCH function - and another after that. I'm trying to keep it all in one column.

Here is an example of the first index match

=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(L2,[FILENAME.XLSX]DealerDetails!$CJ$2:$CJ$15000,0))

If this turns out an #N/A, I'd like those #N/A's to switch to this formula:

=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(M2,[FILENAME.XLSX]DealerDetails!$CK$2:$CK$15000,0))

Then the remaining #N/A's to go to this one:

=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(G2,[FILENAME.XLSX]DealerDetails!$G$2:$G$15000,0))

The column I'm referring to will have around 2,000 records or less.

View 1 Replies
View Related
Dec 15, 2013

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

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

View 9 Replies
View Related
Apr 3, 2014

I have two files

Say first file name is X and in work sheet 1 i have used name manager for two columns say "Series" and "Fund name"

Second file name is Y

R7C3 is Fundname of Y file

RC4 is Series Value of Y file

In the row of Y file , C 15 - I want to calculate the formula

Range("C15").FormulaArray = _

"=IF(ISNA(INDEX(Series,MATCH(R7C3&RC4,Fundname&Series,0))=RC4),""NO"",""YES"")"

I am not getting any error but the result is not correct , even for the series which is not there in my file "X" it says "YES"

See my codes below :

Dim colA As Variant

Dim colB As Variant

Dim WksDash As Workbook

Dim Wksfile As Workbook

Dim wksWatch As Worksheet

Dim WksDash2 As Worksheet

Dim Classunion As Variant

[Code] .....

View 2 Replies
View Related
Jul 1, 2014

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.

=(INDEX('Agent Weekly DataSheet'!G:G,MATCH('Agent Weekly Overall Scores'!B8,IF('Agent Weekly DataSheet'!F:F='Agent Weekly Overall Scores'!G8,IF('Agent Weekly DataSheet'!B:B='Agent Weekly Overall Scores'!C8,'Agent Weekly DataSheet'!B:B),0))))

The bolded piece is what I can't figure out.

View 2 Replies
View Related
May 4, 2009

I have a couple of desired outcomes.

1. I want to find the “Close” price based upon “Date” and “Time” input (search criteria).

2. I want to know when (what time) a “Price” (input) falls between the “High” and “Low”, on a specified date.

DateTimeOpenHighLowCloseVolumeTimeDateTimeCloseTime2/24/200913:00138.21138.23138.18138.22410.022572/24/200913:00#N/ABUY2/24/200913:01138.23138.30138.18138.26680.02260SELL2/24/200913:02138.25138.28138.21138.28340.022632/24/200913:03138.27138.37138.27138.35680.022662/24/200913:04138.36138.41138.31138.33440.022692/24/200913:05138.34138.44138.32138.39330.022712/24/200913:06138.40138.45138.37138.41350.02274

View 9 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
Jun 8, 2014

what i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?

e.g. worksheet 1 has all the data

A B C

email ID date

worksheet 2 has a list of specific email addresses i'm looking for info on

A B

email date

My formula in column B of worksheet 2 is along the lines of this:

index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))

basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.

The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.

View 1 Replies
View Related
Jun 9, 2014

Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:

EnglishSS1 EnglishSS2 EnglishSS3 Rating

10 20 30 3

20 30 40 2

30 40 50 1

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

View 2 Replies
View Related
Jun 22, 2014

with a multiple criteria index match array!

I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!

I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)

View 6 Replies
View Related
Aug 25, 2014

I have a workbook (workbook1) with multiple sheets (sheet a, sheet b, sheet c), all with the same column headings. One of the columns in each sheet of this workbook contains an invoice number.

In a different workbook(workbook2) I need to find the invoice details based on sheets a-c from workbook1.

So, in workbook2 I can input the invoice number in column a and the rest of the details will be pulled through based on whichever sheet (a-c) from workbook1 that the details are in.

Workbook1 is normally closed (from reading others posts, the INDIRECT function might do what I need but would not work with workbook1 closed)

Working with just 1 sheet in workbook 1, the following formula works perfectly:

=INDEX('[workbook1.xlsx]sheet a'!$V:$V,MATCH(A2,'[workbook1.xlsx]sheet a'!$W:$W,0))

How to do the same, but looking in sheet a, b and c at the same time for the answer?

View 2 Replies
View Related
Dec 3, 2013

In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.

In cell B12 i have an index match that I can not get to return a value.

Is Index Match the correct method of returning a result? Or have I just got the formula wrong?

View 1 Replies
View Related
Mar 4, 2014

I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.

I am currently using the formula

=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))

to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?

View 2 Replies
View Related
Dec 29, 2009

hope the below example is clear to understand and makes sense .....

View 10 Replies
View Related
Mar 13, 2014

I've got a problem where I am trying to return a value if two criteria from one table match two from another.

I have included a example of my issue.

=INDEX(Time!D:D,MATCH(Data!A2,Time!A:A,0)*AND(MATCH(Data!C2,Time!C:C,0)))

I have had a look around lots of forums but cant get my head around what is wrong with my formula

View 4 Replies
View Related
Feb 9, 2010

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.

View 14 Replies
View Related
Nov 27, 2006

I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!

I have a worksheet (let's call it DATA) with the following column headers:

* Site Name

* Year Initiated

* Status

plus a whole bunch of other columns of information.

On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.

I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.

Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.

View 12 Replies
View Related
Dec 29, 2011

The below formula was provided by PGC, and works great:

=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=A15,MATCH($E$2:$E$8,$E$15:$E$18,0))))

In post:

Complex Array(?) Search

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

Column A____Column B____Column E

Apple_______Red_________Fair

Apple_______Red_________Poor

Apple_______Green_______Great

Pear________Red_________Great

Pear________Yellow_______Great

Pear________Red_________Good

Pear________Red_________Fair

I want my verification to return all the worst Red fruit:

For Apple Red: Poor

For Pear Red: Fair

View 1 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
Sep 3, 2012

I have a set of values in column A in sheet 1 and a compilation of some of the same set of data in column A in sheet 2. Using the index match function, I have been able to list the corresponding values in column B of sheet 2 onto column B of sheet 1 (for those values which are common to both sheets - matching occurs between column A in both sheets). However, I now have some of the set of data in sheet 2 and the rest in sheet 3 (again, in column A). Is there any way of using index match to search in both sheets and index the corresponding value from column B of the sheet containing the matched value in column B of sheet 1?

I have tried this by nesting the index match functions for each sheet into two separate IF arguments but haven't had any luck. Is there a better way of doing this?

This is what I have currently tried in Cell B2 of sheet 1:

=IF(INDEX(Sheet2!$B$2:$B$3001,MATCH(A2,Sheet2!$A$2:$A$3001,FALSE),1),IF(INDEX(Sheet3!$B$2:$B$3001,MATCH(A2,Sheet3!$A$2:$ A$3001,FALSE),1)))

Excel keeps saying this formula has errors but I have no idea as to what changes I need to make to get the formula to model my situation. Then again, I'm not even sure if this is the approach I should be taking to create a formula that models my situation.

View 1 Replies
View Related