# Index / Match With Multiple Name Ranges

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
ADVERTISEMENT
Sep 19, 2013

I have two columns both of which lengths will vary depending on the time-frame entered by the user but will be the same as each other for each run of the code. Because of that, I'm trying to create a named range for each column and then execute an Index Match with an Offset off of it. Below is my code which results in an "Object required" error message.

Code:

Dim Ticker As Long, Tick As Range, TIK As Range, RoleDt As Long, RD As Range, endDate As Variant

Ticker = Range("F" & Rows.Count).End(xlUp).Row

RoleDt = Range("E" & Rows.Count).End(xlUp).Row

Set TIK = Range("F2:F" & Ticker)

Set RD = Range("E2:E" & RoleDt)

cell.Formula = "=INDEX(RD,MATCH,(RC[-1],TIK,0)+1)"

View 9 Replies
View Related
Dec 28, 2006

B2 contains CITYNAME

A2 contains The name of the TAB the data relates too (although i cant find anyway of using this cell within a formula to read the relevant tab, maybe you know of a way)

This is the working code i currently have in cell B3, this is what i need to enhance.

=SUM(INDEX(ABCD!$1:$65536,0,MATCH(B2,ABCD!$1:$1,)))

This looks at the tab ABCD and sums all the values it finds in the column that matches the name supplied in cell B2 on Sheet1

Tab ABCD has dates within coulmn A, and Values in Column B, first row from Column B to Column Z contains the CITYNAME (coumn Z may become longer so this needs to be able to cope with that too.

What i would like to do is add into this an extra requirement, there must be a start and end date. I still need to use the match function as B2 changes using a drop down validation (containing CITYNAMES, these represent the columns within the tab ABCD

E1 Contains the STARTDATE

H1 Contains the ENDDATE

This is the closest I get, changing the start and end date does not give the expected result...

=SUM(--(ABCD!$1:$65536>=E1),--(ABCD!$1:$65536

View 9 Replies
View Related
Sep 8, 2009

Column A contains State names; column B contains a few cities for each of the states. Row 1 contains rank as the header row. Then the table begins in cell C2 with the rates. I have two of these (on separate sheets), one titled w dep (with dependants) and the other w-o dep (without dependants). On a third sheet I have 4 combo boxes.

1.Chooses between Dep or W-O Dep

2.Chooses Rank

3.Chooses State

4.Chooses City

Here are my questions.

•How can I filter down the cities so that if I chose Texas for example, only the cities from Texas would show up in my combo box?

•I have figured out the formula to display the results from the w dep sheet but cannot get the information from the w-o dep sheet. Here is the formula I’m using.......

View 3 Replies
View Related
Sep 26, 2011

I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..

Here is the code:

Sub test1()

Dim myvalue As Variant

Dim wsname As String

Dim i As Integer

Dim j As Integer

Dim r1 As range

Dim r2 As range

Dim r3 As range

Dim r4 As range

wsname = "CustomerIDbyParts"

[Code] .......

The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?

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