# INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008INDEX/MATCH multiple ocurence match values needed

View 9 RepliesINDEX/MATCH multiple ocurence match values needed

View 9 RepliesADVERTISEMENT

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 have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.

One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.

Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.

2011

2011

2011

2012

2012

2012

[code].....

I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed

View 9 Replies View RelatedI am trying to do an INDEX/MATCH to return a value based on multiple lookup_values.

The workbook is attached, and what I am trying to accomplish is this:

If 50, 75, or 90 are selected from Column D drop down of this (Main) worksheet, then return Column A, B, and C values for those rows to the Report worksheet.

So in my sample spreadsheet, John, Jim, and Jack's information should be returned on the Report worksheet because their Column D shows 50, 75, and 90. Alice, Wanda, and Cindy's information should not be returned on the Report worksheet because their column D shows Budget Only, Contacting, and 100.

My current INDEX/MATCH formula is =INDEX(Main!$C2,MATCH(50,Main!$D2),0), but this only works when column D has 50 chosen because I have only asked MATCH to return info if it finds something with 50 because I do not know how to ask it to search for multiple lookup_values.

So, as you will see, only John's information is returned on the report worksheet, but Jim and Jack's are not.

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 Replies View RelatedI 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.

I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)

View 2 Replies View RelatedWe know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.

sampleexcelhelp.xlsx

The columns that need to be filled are shaded in dark pink.

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

Here's my table:

2014 2014 2014 2013 2013 2013

Jan Feb Mar Jan Feb Mar

A 56 63 100 78 25 44

B 48 12 74 32 65 97

C 45 85 96 42 82 73

How will I use index match function to get the data that correspond to each criteria? For example I am looking for the value of A in Mar 2013.

See my attached spreadsheet. I am trying to get start dates and end dates based upon rates. The first start date is really simple, I just use A2. In F2 should return the last date from A2:A27 where the rate is I2. In E3 The I would like to return the first date in A2:A27 where the rate is G3. and so on. If the rate is 0 then the date can be blank or 1/1/1900.

View 2 Replies View Relatedi have over 1000 line invoice # with multiple listings....however they have different dollar values for each item

702-0195660 = $10

702-0195660 = $12

702-0195660 = $15

i have an index match in my "missing worksheet" **see attached**

but that is looking up only the first value

702-0195660 = $10

702-0195660 = $10 (should be $12)

702-0195660 = $10 (should be $15)

etc

i have attached the sheet..ive been on the past day!

attached file link below [URL]...

I'm running a College Football Bowl Game pool. NO money just fun! I've just about got this down except that my Standings area for this years members is not working like I want. There are 24 members and I want them ranked 1-24. This works however, when 2 or more members have the same score....only the first name in the column with that score appears.

Vea, Ray WA955

Braham, LukeHI916

Braham, LukeHI917

Bowers, LarryGA908

This cell comes back with 3 different dupes:

=INDEX(H$1:CB$1,1,MATCH(D87,H$83:CB$83,0)-1)

H1:CB1 are the Names

H83:CB83 are the points for each name

D87 comes this cell : =LARGE(H$83:CB$83,E87)

How can I tell Excel to show all 24 names including the dupes?

I am trying to retrieve values with INDEX and MATCH. I have column B that contains "i", and "m".

I have formulas for the first occurence of "m"

=(INDEX(NUP!$J:$J,MATCH("m",NUP!$B:$B,0)))

Which will look for the first "m" and output the value in column J that aligns with the "m"

How would I alter this or a new formula for the nth occurrence of "m"?

I'm trying to do in Excel 2000 that seemed way easier before I tried to write the formula. Any thoughts would be much appreciated.

I have some data setup more or less like this:

1State DeptJan Feb Mar

2California 50128 31 6

3Utah 20119 3 11

4California 20122 49 22

5New York 301 29 51 15

I'm looking to write a formula to retrieve the numerical value located in the cell for a particular month, across from a particular department number and state - so I can plug it into another spreadsheet.

I tried the following formula for January, but I get an "N/A" error when I hit 'Ctrl+Shift+Enter' to use it...

=INDEX(C2:C5,MATCH(1,("Utah"=A2:A5)*("201"=B2:B5),0))

Can you not use static values in this type of formula?

Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.

View 10 Replies View RelatedI couldn't resolve the formula of Index-Match for getting the values that matches the following criteria

1) Match the "Category" of Table 1 to Table 2

2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.

Once the above conditions are met, Take the Result from Table 2

Table 1 is for Input and Table 2 is for Database.

Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didn't work out.

Have a look at the file attached : IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx

I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:

View 3 Replies View RelatedI am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):

{=IF(ISBLANK(G$9),"",SUM(IF(INDEX('Journal'!$A$20:$Z$1020,MATCH("Prior",'Journal'!$F$20:$F$1020,0),MATCH(G$9,'Journal'!$H$19:$Z$19,0)),"")))}

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

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.

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

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

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

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.

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

Copyrights 2005-15 www.BigResource.com, All rights reserved