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
ADVERTISEMENT
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
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
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
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
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
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
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
Oct 8, 2009
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 Related
May 23, 2014
We 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.
View 5 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
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 10, 2009
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 Related
Jan 15, 2013
I 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
View 3 Replies
View Related
Oct 6, 2009
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 Related
Sep 27, 2007
I 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)),"")))}
View 9 Replies
View Related
Apr 6, 2009
Example:......
On my worksheet 1, I have two dropdown boxes: B7 (that gives the building name) and B11 which gives the Type of Commission (Renewal, Expansion, New). B 20 is supposed to automatically select the percentage based upon what B7 and B11 select. E.g. B7 = House, B11 = Expansion then B20 should be 3.
This is my formula.
=HLOOKUP(B7,'Building Details'!$A$2:$R$24,MATCH(B11,'Building
Details'!$A$2:$R$2,0))
View 3 Replies
View Related
Jan 8, 2014
I am using sheet 1 to pull data from sheet 2 using this formula
=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0)).
This on its own works to pull the data. However the data is repeated for each 'set' of data ie each group of repeating names, as I drag down. I just want the data to appear once on the first lie of each group so I have added an if to the formula
=IF(C2=C1,"No Match",
My complete formula is now
=IF(C2=C1,"No Match",=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0))
View 2 Replies
View Related
May 3, 2009
On the attached workbook, on the "Progress Summary" page, cell C30 has a formula which should list the names of pupils who made 0 progress (as referenced by the Raw Data page, column R [progress] and column A [name].)
View 3 Replies
View Related
Dec 3, 2009
I have never used the aforementioned functions before, but i assume thats what i need to do here....
In the attached table, what formula do i use to return the value '8' i.e. whats in column "Epsilon" and row "Three"
If you could let me have the generic formula i need as well, that would be good.
View 11 Replies
View Related
Feb 3, 2010
I can't figure out why the N/As are coming. In G2 I have =INDEX(Area_Codes!$D:$D,MATCH(F2,INDEX(Area_Codes!$B:$B,0),0)). If I replace F2 with 407, I get FL which is right. But why do I get N/A??
View 4 Replies
View Related
Feb 18, 2010
L26 returns incorrect information. Input parameters are F29 and F30. In its current state, it returns the value “b”, where I think it should be returning “1”, i.e the intersection point for “3Ph” & “Single-core 70°C thermoplastic non-arm Cu Table 4D1”
View 6 Replies
View Related
Mar 5, 2009
If the value on sheet2 columnA ,matches value sheet1, column AC
then corresponding row value sheet2,column E be put in corresponding row for matched value ,sheet1,column Z.
View 6 Replies
View Related
Mar 30, 2009
I have the following array formula that matches against a table with 3 criteria (one specfically is a time), it works ok until the time criteria is not of the value searched for, it may be +/- 30 minutes out, is it possible for the formula to index/match to the actual value or to find the nearest match possible, its in column B.
View 3 Replies
View Related
Apr 27, 2009
insert =IF(ISNA into the following formula? =INDEX(Data!$AE$2:$AE$10,MATCH(1,(Data!$AI$2:$AI$10=$A$11)*(Data!$AD$2:$AD$10=Sheet1!$A$6),0))
View 3 Replies
View Related
Dec 17, 2006
I have the following code from my UserForm that works fine for my Summary sheet.
Private Sub TextBox2_AfterUpdate()
Application.ScreenUpdating = False
'//Get the Truck # to act on
Dim varInput As Variant
varInput = TextBox2.Value
'//Exit if no input
If varInput = "" Then
Exit Sub
End If
What I am trying to figure out is to do a Index,Match or what ever it takes to populate the Truck sheet (250+) with info from the user form.
TextBox1 is the date. TextBox2 is the truck number, 3 is the Milage, and 4 is the Cost.
All in relation to the following sheet for the truck: ...
View 9 Replies
View Related
