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.

View 2 Replies


ADVERTISEMENT

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

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?

View 7 Replies View Related

Match And Index Functions

Jul 2, 2006

I am working on a scheduling system and am trawling through some old notes of some two or three years. I came accross two formulas, which I must have used a while ago. they are a little more baffling that what I have recently used, but I must have referenced them for a reason. Normally I write down explanations of the formulas in blonde, so that I can understand their reasoning at a later date, however, I can find no reference to them. explain what they actually do in a logical fashion. I have been looking at this for four hours, filling in date ranges and cells that they refer to, but cannot make sense of them.

=MATCH(E6,Caljay) + (MATCH(E6,Caljay)=MATCH(E6,Caljay))

=INDEX(Caljay,F6,1)-(INDEX(Caljay,1)-MAX(INDEX(Caljay,F6,1),E6))

View 3 Replies View Related

Index And Match Worksheet Functions

Aug 29, 2009

I am learning to use the index and match worksheet functions.

I read through some examples and tried to set up a spreadsheet as attached.

For some reason, it is working only partially. For some cells the value is returning correctly and in some it is returing #ref.

View 2 Replies View Related

Nest Match & Index Functions

Jun 1, 2008

I would like to use a custom function or excel formula, if already exists, that finds the text "12 months" and then returns the value of the column that holds that value.

View 9 Replies View Related

Index And Match Functions In Macro Code

May 26, 2008

I want to write the following function using VBA. =index(B2:D8, match(lookup_value, A2:A8, 0), match(lookup_value, B1:D1, 0))

View 8 Replies View Related

Conditional Formatting With INDEX/MATCH Functions On Seperate Worksheets.

Apr 16, 2009

I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.

Worksheet (functions!)

This one has a list of numbers formatted as general. (Column G)

Is actually a formula/macro that outputs a number... (didn't know if this mattered?)

View 6 Replies View Related

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.

See the attached spreadsheet example for ready made tables and formulas : problem.xls‎

View 4 Replies View Related

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.

View 9 Replies View Related

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

View 11 Replies View Related

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.

View 2 Replies View Related

Excel 2011 :: Can't Get Index Match Max Functions To Work Sampling Data From Multiple Columns

Jan 29, 2014

I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.

I tried a few of the index match max formulas I found here and could only get them to work with one column of data.

I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.

I am using Excel 2011 for Mac

View 7 Replies View Related

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

View 14 Replies View Related

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.

View 3 Replies View Related

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?

View 1 Replies View Related

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.

View 3 Replies View Related

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

View 5 Replies View Related

INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies View Related

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.

View 4 Replies View Related

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.

View 2 Replies View Related

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!

attached file link below [URL]...

View 9 Replies View Related

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

View 5 Replies View Related

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?

View 9 Replies View Related

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

View 5 Replies View Related

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?

View 4 Replies View Related

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

View 6 Replies View Related

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.

View 4 Replies View Related

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.

View 9 Replies View Related

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

View 2 Replies View Related







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