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?
View 1 Replies
ADVERTISEMENT
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.
View 2 Replies
View Related
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.
See the attached spreadsheet example for ready made tables and formulas : problem.xls
View 4 Replies
View Related
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.
View 9 Replies
View Related
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
View 11 Replies
View Related
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.
View 2 Replies
View Related
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
View 14 Replies
View Related
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.
View 3 Replies
View Related
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?
View 1 Replies
View Related
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.
View 3 Replies
View Related
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].....
View 5 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
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.
View 4 Replies
View Related
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.
View 2 Replies
View Related
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!
attached file link below [URL]...
View 9 Replies
View Related
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"?
View 5 Replies
View Related
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?
View 9 Replies
View Related
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%?
View 5 Replies
View Related
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?
View 4 Replies
View Related
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
View 6 Replies
View Related
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.
View 4 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
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
View 2 Replies
View Related
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
View 9 Replies
View Related
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.
View 7 Replies
View Related
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
View 9 Replies
View Related
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
View 6 Replies
View Related
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]...
View 4 Replies
View Related
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
View 10 Replies
View Related
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?
View 7 Replies
View Related
