# Duplicate Values From Index / Match

Dec 23, 2011

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?

## Using INDEX / MATCH Functions With Duplicate Values

Apr 12, 2013

I receive data (A1:E2) which is sorted alphabetically accordng to name:

AaronBobConDanEd
3-Jan-135-Jan-133-Jan-131-Jan-132-Jan-13

I use the SMALL function to sort the data in date order e.g: A6= =SMALL(\$A\$2:\$E\$2,1) = 1/1/13, B6= =SMALL(\$A\$2:\$E\$2,5) = 2/1/13, etc:

1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13

I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX(\$A\$1:\$E\$1,MATCH(A6,\$A\$2:\$E\$2,0)) = "Dan", B7= =INDEX(\$A\$1:\$E\$1,MATCH(B6,\$A\$2:\$E\$2,0)) = "Ed", etc:

1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
DanEdAaronAaronBob

The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".

How to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.

The formulas always assume the data is in multiple rows rather than multiple columns.

## Match / Index Duplicate Value

Jul 6, 2010

I have a table with 3 columns, the first being static text "issue types" and the second two dynamic values created from counting instances of each issue type from another sheet.

I want to create a table of the top 5 issues which updates automatically. I can use the LARGE function to list the top 5 values, and then need to match these to the issue type.

Where I'm having problems is when there are duplicate numbers for results in the top 5, it will match the first instance 'issue type' for all duplicated entries. Instead I need to put a check in place so that it moves down the table rows to the next instance of the matched number and indexes that issue type instead.

## Using Index And Match When There Are Duplicate Look-ups

Oct 9, 2007

I'm using the following simple formula to lookup a value in a table:

=INDEX(\$A\$3:\$D\$8, MATCH(F5,\$A\$3:\$A\$8,), MATCH(G3,\$A\$3:\$D\$3,))

where the lookup values are in the Header A3-D3 and in Column A3-A8 (contains dates).

It looks up the value I request in F5 and G3.

Works great until I notice that sometimes the date values in Column A3 can be duplicated. When this occurs, it returns only the corresponding value from the first date it "sees". I need it to return the data for both somehow.

I realize I could use vlookup for this but it would be to cumbersome in my next application.

## Index With Match With Duplicate

Jun 11, 2009

i am using Index With Match and the criteria for match is date
and it duplicate

how i can make it get all duplicate value

## Index Match Small With Duplicate Value?

May 15, 2014

I have an equation below that work great except when row 2 has a duplicate value. When this happen row 5 only return the first value in row 1 and not the other duplicated values.

=INDEX(\$B\$1:\$AF\$1,MATCH(SMALL(\$B\$2:\$AF\$2,COLUMN(B\$1)-1),\$B\$2:\$R\$2,0))

see attachment. How can I have row 5 return the following value: B5 = A, C6 = B and D5 = C.

## Excel 2010 :: Index / Match Duplicate

Jul 18, 2014

I have a victim of the Index-Match duplication problem in Excel (2010). Basically, I have three columns of data, all daily input for the year.

Column 1 = Date
Column 2 = Actual (Units Sold)
Column 3 = Scheduled (Units Sold)

The Date is filled out through the end of the year as is the Scheduled values.
The Actual values are filled out daily.

I need to generate a summary box that reports Actual, Scheduled, and Variance (Actual - Scheduled) for the time periods Daily, Month to Date, and YTD.

My problem is that when I try to return the Schedule value that corresponds with the date of the last entry, I don't know if I am pulling the correct Schedule value since I do not know if the Actual value (that is pulled from the last value in the Actual column) is unique. So I tried using an Index-Match formula to return the latest value (that is the last record occurrence of the value) to my function in order to retrieve the correct Schedule value, but, sadly, it did not work.

I by no means am an Excel expert like many of you, so I may have some questions along the way.

I've attached a sample extraction from my worksheet and included an example of the Summary panel I'm creating.

ActualVsScheduled_problem - Copy.xlsx

## Index / Match In Macro With Duplicate Matches

Jun 3, 2014

My user has a worksheet that is hundreds of rows long. Column A contains the SerialNumber, col D contains the PartNo and col E contains the SerialNo. She wants her formula in col B to match the SerialNumber with SerialNo then return the value from PartNo into col B. =Index(PartNo,Match(A2,SerialNo,0)) gets me the match that I'm looking for so I thought I was done. Not so...she now tells me that there are duplicate, triplicate, etc., matches and the formula obviously is only pulling in the first match.

When she has a duplicate, she needs to have cells inserted into Col A & B. She does not want a whole row inserted because the PartNo and SerialNo will already be in the right places. I've tried inserting a helper column to enumerate the duplicates but I can't it to work. I also tried using ROWS in the formula to work with the duplicates but that only confused me. I'm pretty sure I'll need a macro to do this but I'm getting nowhere.

## How To Handle Duplicate Data Using Index And Match Function

Feb 4, 2014

How to deal with duplicate data in excel using index and match function. Is it possible to use the following function without having a unique value?

## VLOOKUP / INDEX And MATCH To Return All Values That Match

Feb 4, 2014

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.

## Using Index / Match Function For Two Column Match Values

Aug 28, 2012

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

## INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

## Index Match For 3 Lookup Values?

Jun 9, 2014

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.

## Index And Match Dates And Values

Jan 8, 2010

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.

## Index Match For Unique Values

Dec 22, 2011

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

## Retrieve Values With INDEX And MATCH

Jan 30, 2014

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

## Match And Index With Two Lookup Values

May 30, 2007

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?

## Automatically List Values Using INDEX And MATCH?

Mar 21, 2014

I have created a sales pipeline list in a workbook. One of the sheets ("Contacted potentials") has the following setup:

A---------------------B---------------C----------------D
Company name--------Location--------Probability--------Total company sales

Rows 1, 2 and 3 have other information. The actual list starts at the 4th row.

In another worksheet ("Overview"), I want to create a list of companies that have agreed on working with us (displayed by a probability of 100%). This should preferably be done in column A. Furthermore, column B should list the total company sales for that specific company. I have tried this myself using INDEX and MATCH formulas, by using the following formula:

=INDEX('Contacted potentials'!\$A\$4:\$A\$1000,MATCH(100%,'Contacted potentials'!\$C\$4:\$C\$1000,0))

But this only displays one of the companies, not all those which have a probability of 100%.

Ps. it is possible to automatically update this list (by pressing F9) when a company's status changes to 100%?

## Index And Match Function Returning Same Values

Dec 15, 2009

In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?

## Returning Second / Third Etc Values Using VLOOKUP / INDEX / MATCH

Mar 1, 2014

making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results

## Return List Of Values Index/match

Aug 26, 2009

I have a list of data that I want to average the number in column B when there is a match in column A

COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500

What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.

## Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

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.

## Using Index / Match / Mini To Find Three Lowest Values In A Row

Apr 22, 2014

I've used the below formula to fill column D with the name of the cheapest supplier for the parts listed in each row.

=INDEX(\$G\$1:\$M\$1,MATCH(MIN(G3:M3),\$G3:\$M3,0))

Is there a way to fill columns E & F with the 2nd & 3rd cheapest suppliers?

My table is shown below:

Capture.PNG

## Multiple Match Values - Index Dynamic Table

Aug 19, 2008

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

## INDEX / MATCH Using Multiple Static Lookup Values From Dropdown

May 20, 2014

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

## Using LARGE Formula With Index / Match To Pull Values When There Are Duplicates

Sep 13, 2012

I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?

I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.

B1:

[Code]....

C1:

[Code]....

Warehouse S/S %
WH 1 50.00%
WH 2 57.14%
WH 3 0.00%
WH 4 50.00%
WH 5 100.00%
WH 6 60.00%
WH 7 33.33%
WH 8 66.67%
WH9 60.00%
WH 10 63.64%
WH 11 78.57%
WH 12 55.56%
WH 13 42.86%
WH 14 71.43%
WH 15 61.54%
WH 16 71.43%

Attached File: Book1.xlsx‎

## INDEX MATCH And MIN / SMALL To Pull First And Second Smallest Values From Array

Jan 18, 2014

I'm using this formula to lookup data based on the lowest average.

[Code] .....

The problem is that the minimum average is a two way tie, and I need to return both data sets. It just happens to have the same average.

I've tried using SMALL(array,1) and SMALL(array,2) in place of MIN above, but it gives me the same data set. I've also tried resizing using offset.

See attached : sample.xls‎

## Adding Values To Bottom Of Row In Range In Excel After Index Match

Jul 12, 2013

Basically, I am trying to write a program that will index match through a range and if it doesn't find the value that it is matching to, it will add the value to the bottom of the range it is indexing against. In other words:

My C column has a list of CUSIPS. In column J, I would like for the User to be able to add a list of cusips and then have the ones that don't already exist in the list of CUSIPS in column C be added at the bottom of the range. I have written a macro now that uses the index match, but for some reason it adds all of the cusips that exist in J range to column C, not just the ones that are missing. I can provide a template if necessary.

Sub Filler()
Dim Row As Variant
Dim NumberOfRows As Long
Row = 0

[Code]...

## Index Match With Small For Generating Result In Ascending Order Even If Values Same

May 19, 2014

Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name

means if values are A=1 B=2 C=3 D=4 E=2

it has to show like this A B E C D

But the result is coming like this A B B C D

Formula which i used

=OFFSET(B\$6,MATCH(SMALL(C\$6:C\$18,ROW()-ROW(C\$6)+1),C\$6:C\$18,0)-1,0)test.xls

Here i am attaching the sample file for your reference

## Offset / Index / Match - Using 3 Functions To Search For And Return Values From Data Sheet

Jan 19, 2014

Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.

The attached spread sheet has performance data for a group of employees.

What I need to do is find a particular employee then return a value for one of the category's.

For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.

Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.

Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.

Maybe Offset-Index-Match is not even the way to go here?