Using INDEX & MATCH With MAX And LARGE
I have an Excel sheet on which I need to calculate the largest, 2nd largest and 3rd largest volumes from the range of data.
I've done this using an INDEX and MATCH formula to return the Status # for the largest, 2nd and 3rd largest volumes. So the formulas are:
Rank 1
=INDEX(B3:G3,MATCH(MAX(B3:G3),B3:G3,0))
Rank 2
=INDEX(B3:G3,MATCH(LARGE(B3:G3,2),B3:G3,0))
Rank 3
=INDEX(B3:G3,MATCH(LARGE(B3:G3,3),B3:G3,0))
However when the volumes are the same the formula returns the same Status # for both. For example below, for Site 1; Status 3 is ranked 1st and then Status 1 is ranked 2nd, for the 3rd rank, the formula is returning Status 1 again.
Is there any way of changing the formula to return Status 4 as the 3rd ranked instead of returning Status 1 again?
Status 1Status 2Status 3Status 4Status 5Status 6Rank 1Rank 2Rank 3Site 18655802Status 3Status 1Status 1Site 241818691Status 2Status 2Status 5Site 389126220Status 5Status 3Status 2Site 43591872Status 4Status 3Status 5Site 51650015Status 1Status 2Status 2
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
 Index Match Large
 Index/Match/Max
 Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately
 Want To Display The Cell Next To A Max/large Number.
 LARGE() Doesn't Work When Having Multiple Max Values
 VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??
 Max Index Lookup Returning Two Cells?
 INDEX / MATCH Multiple Ocurence Match Values
 Sum If W/ Index Match On Vertical And Horizontal Match
 INDEX, LOOKUP Or Find The Top 5 MAX Orders
 Lookup/IndexMatchMatch Using Segments
 Adding A Third Match To An Index Match Formula
 Hlookup And Match Or Index And Match?
 Using Match & Large To Rank Values.
 Offset, Match, Max Formula
 Index Match
 Index, Match Etc
 Match The INDEX
 Index And Match The Value
 Various Index Match
 INDEX And MATCH
 Index Match Getting #N/A
 Index / Match
 Index,match = #n/a
 Index/match Within Certain Value
 SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??
 Index Match Formula
 Index Match Error
 Index/match Formula
 Index Match Offset
 Index Find Next Match
 Index Match Across Workbooks
 Index(Match) Formula
 Match/index/vlookup
 Reverse Index/Match
 Vlookup Or Index/match
 Index With Match With Duplicate
 Index, Match Button
 Index Match Formula
 Index/match To Get Single Value
 INDEX MATCH Two Criterion, AND/OR
 Index, Match For VBA
 Index And Match Funtions
 INDEX / MATCH Giving #value
 Index Match The Maximum Value
 Vlookup OR Index And Match.
 Index Match Several Returns.
 Index Vlookup Match
 Index, Match For Highest Value
 Index, Match In Table
Index Match Large
I've attached my condensed softball team spreadsheet At the bottom, I'm trying to show the stat leaders, and the problem is the ties. For example, batting avg, there are 2 players tied for 2nd at 0.750, how can i have the name column show each of the 2 tied players (Paul and Nathan)? I think this thread shows what i'm trying to do, but i can't adapt it to my spreadsheet.LARGE Function with Numbers and Text
View Replies!
View Related
Index/Match/Max
This is what I'm looking for. I'll try to give an example. A B C D E 1 2 3 4 5 6 7 8 9 2 =SUM(A2:A3) =SUM(B2:B3) ... =MAX(A4:E4) This maximum would be 13. I actually want it to show the name (first row) of the column were the max occured. In this example it would be "D" since 4+9 is the maximum :p I searched quite alot of sites but I'm having difficulties understand the exact meaning of the functions. I believe it can be solved with index/match/max. Hence the title, vloopup/hlookup can be possible, but I believe it's not what i'm searching for.
View Replies!
View Related
Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately
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 Replies!
View Related
LARGE() Doesn't Work When Having Multiple Max Values
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule. Example: In this row: 7 7 6 5 5 4 I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7. How do I get the second value that I want? Maybe if I use the RANK() formula in some way, but how?
View Replies!
View Related
VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??
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 Replies!
View Related
Max Index Lookup Returning Two Cells?
I have a formula I need to modify. In column b I have a list of items by number, each one is unique In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times. In column d I have a list dates for each of the items listed in column c In column e I have a list of prices for each of the items listed in in column c The idea with the orginal formula was to look up the item and return the date =MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0)) Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that? I trying to return the most recent date and price for a item. I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
View Replies!
View Related
Sum If W/ Index Match On Vertical And Horizontal Match
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 Replies!
View Related
INDEX, LOOKUP Or Find The Top 5 MAX Orders
Columns F looks at another table within the same worksheet for the Customer Code that has the MOST Orders in Column B.....then places the Tot Orders amount in F2..... Cust Codes in Column E and the Cum Values into Col G ( that's already been accumulated and housed in column C ). Cell F3 then go looks up the next largest order in Column B and pulls over the adjacent Customer Code & Cum Value to the E,F,G table again. Basically there's 3 functions: 1 in E2 1 in F2 1 in G2 All to copy downward within that table ******** ******************** ************************************************************************>Microsoft Excel  Book4___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1= ABCDEFG1CustomerTot*OrdersCum*Value.CustomerTot*OrdersCum*Value2Cust*682$943.Cust*682$9433Cust*973$787.Cust*973$7874Cust*1055$525.Cust*1055$5255Cust*840$438.Cust*840$4386Cust*125$150.Cust*125$1507Cust*27$300....8Cust*55$180....9Cust*43$75....10Cust*31$25....11Cust*71$12....Sheet1* [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Hlookup And Match Or Index And Match?
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 Replies!
View Related
Offset, Match, Max Formula
I found the following formula, with different sheet and cell references, on someone's site: =OFFSET(CurvCalc!$E$12,MATCH(MAX(CurvCalc!$E$12:$E$10000)+1,CurvCalc!$E$12:$E$10000,1)1,0) I googled for the last couple of days but can't find this site again. I have found several that come close like Dave's at http://www.ozgrid.com/News/exceldynamicrangesvba.htm http://www.cpearson.com/excel/lookups.htm http://www.cpearson.com/excel/lists.htm and Microsoft's at http://support.microsoft.com/default...;enus;Q152407
View Replies!
View Related
Index Match
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 Replies!
View Related
Index, Match Etc
I am having trouble finding the right formula to use to get the data I want. Let me outline it below: Worksheet 1 contains raw data: Column B : Date/Time i.e 8/1/2006 9:31am in one row, 8/1/2006 9:32am in the next row etc.. Column C: Temp i.e 33.5 Column D: Hum etc... Worksheet 2 is a summary worksheet and contains: Column A : Date i.e 8/1/2006 Column B: Time i.e 9:31am Column C: Max temp i.e 35.8 (calculated via array formula) I wish to use a formula (which I can copy down the worksheet so it needs to apply to the whole column of the reference worksheet) to find the time at which the max temp occurred on each particular day. I don't want to use individual references for each day as it will take too long!
View Replies!
View Related
Match The INDEX
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” & “Singlecore 70°C thermoplastic nonarm Cu Table 4D1”
View Replies!
View Related
Various Index Match
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 Replies!
View Related
INDEX And MATCH
I have Details sheet with this data: A B C 1 Color Name Update 2 blue item1 TRUE 3 red item2 TRUE 4 blue item3 FALSE 5 green item4 N/A 6 green item5 FALSE I need to do lookup within column C (Update) for items that have FALSE and list them on Main sheet. I need to list both: Color and Name. I would like to have output starting on cells A50 and B50 and go down. Main sheet should look like this: A B 50 blue item3 51 green item5 I tried to accomplish this using LOOKUP, INDEX and MATCH but I can't make it to work.
View Replies!
View Related
Index / Match
I have an Excel sheet that uses index and match to lookup different items. The following formula is in cell C7: =INDEX($A$1:$D$4,MATCH(B7,INDEX($A$1:$D$4,,1),0),MATCH(A7,INDEX($A$1:$D$4,1,),0)) The user enters a part number into B7 and a size into A7 and a price is returned into C7 (from a1:d4 range). Is it possible (after removing the formula from C7), that a user enters the price into C7 and formulas in A7 and B7 would be able to tell from the price entered into C7 what model and size it refers to?
View Replies!
View Related
Index,match = #n/a
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 Replies!
View Related
Index/match Within Certain Value
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 Replies!
View Related
Index Match Formula
Guys im stuck on the fact of how to pull the correct information through for a named person.as from the example if i place say steve in the named box i would like all of steves scores to appear in the designated boxes.and so on.
View Replies!
View Related
Index Match Error
i have a table and i want search my table with index match my index match works but when the data is not in my table i get error #N/A (#n/b) how should i get rid of this error?
View Replies!
View Related
Index/match Formula
Current I am using two spreadsheets for similar press and am combining the two. What I need help with this time is currently in cell c9 the formula is =ROUNDDOWN(17.7/C7,0) or =ROUNDDOWN(39.3/C7,0) depending on which press I am using. The presses are shown in cells d34  q34. Indigo =17.7 gilboa = 39.3. I have added these numbers to a table in the scales tab a24:e26. Is there a way to write a formula in cell c9 on the input tab to combine the 2 above formulas and insert the correct 17.7 or 39.3 when required?
View Replies!
View Related
Index Match Offset
I'm having trying to set up a new workbook to pull selected results from an existing one. I am trying to figure out the formula/function for cells B2:D3 on workbook2: EXISTING WORKBOOK1: (Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is dynamic range 'mtd') A B C D Joe P1 7 P2 1 P3 2 Total 10 Jill P1 3 P2 4 P3 1 Total 8 NEW WORKBOOK2 A B C D P1 P2 P3 Joe 7 1 2 Jill 3 4 1 I entered the following as an array formula in cell B2 and it works fine to return the proper result of 7: =INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0)) This does not work to return the results for P2 or P3, I am assuming because I need some sort of an offset for the different rows in workbook1. I am hoping someone can point me in the right direction here. First I want to locate the proper name in workbook 1 and then the proper product under that name and finally pull the mtd col D result for that product and name.
View Replies!
View Related
Index Match Across Workbooks
I have more than workbook (containing a lot of data ) , each of them has 2 columns ( names and codes) I need to use VLOOKUP in another workbook , with 2 coulmns also , to get the name relevent to the code that I enter in coulmn A , so that the name appear in the next cell in coulmn B
View Replies!
View Related
Index(Match) Formula
Need to place an Index(Match) formula in Sheet 2 B2 (as for example). The lookup values in Sheet 2 to be matched with Sheet 1 will be A2, B1 and J1. In Sheet 1 (Edit) I'm only interest in the subtotal and Sheet 2 J1 represents the subtotal for the week that ends with J1 date. The Index should be the whole Sheet 1 (if possible) for further expansion (and I do have way more than what is presented below). Edit HTML removed by RAM and replaced with a new HTML further down.
View Replies!
View Related
Match/index/vlookup
I have two worksheets that I am hoping to use to generate one report in excel. Sheet1 contains the following information for my entire work center: Benefitor Account Dollar Value Document # Sheet2 contains a list of Benefitors that are relevant to only me. I want to pull the information from Sheet1 where the benefitors on Sheet1 match the benefitors listed on Sheet2 into Sheet3.
View Replies!
View Related
Reverse Index/Match
I've got a chess board in B2:I9, and would like a function that returns the location of a particular piece. Say I have Q1 in cell B9, I'd like to return A1 B1:I1 = A > H A2:A9 = 8 > 1
View Replies!
View Related
Vlookup Or Index/match
I am trying to get the info shown on table 1 into table 2. I suspect index and match is the answer, but I am not sure. Table 1 Employee # Deduction Amount 101 AD&D 7.00 101 LIFE 4.00 101 LTD 12.00 101 STD 6.00 101 HEALTH 300.00 101 DENTAL 25.00 105 AD&D 8.00 105 LIFE 6.00 105 LTD 6.00 105 STD 300.00 105 HEALTH 25.00 105 DENTAL 8.00 112 AD&D 7.00 112 LIFE 5.00 112 LTD 13.00 112 STD 5.00 112 HEALTH 450.00 112 DENTAL 37.00 Table 2 AD&D LIFE LTD STD HEALTH DENTAL 101 105 112
View Replies!
View Related
Index, Match Button
I need a button that will referece 2 cells and take me to the intersection of them in a table below. For example, I have a property name in C2 and a date in O7. Down below in a seperate table I have detailed information for that property on that date. When I click the button, i want to automatically go to that occurance.
View Replies!
View Related
Index Match Formula
I have attached a formula I have used to extract a value from another sheet (I have also attached where the formula is mean't to extract the value from) . My problem is that this formula is not returning what it should? The value that should be returned is "416"
View Replies!
View Related
Index/match To Get Single Value
I have 2 spreadsheets with Spreadsheet #1 All Vendor Transactions Column D Column F Column V Vendor Name Invoice Number Check Number ABC 123 ? Spreadsheet #2 Checks 1st QTR 2007 Column B Column D Column E Vendor Name Invoice Number Check Number ABC 123 99777 DEF 246 96504 XYZ 123 98654 I need to get Checks 1st QTR 2007!E1 Check number into All Vendor Transactions!V1 I could have done a VLOOKUP, but many of the values in Checks 1st QTR 2007!D1:59999 are duplicates as different vendors use the same invoice numbers. For various reasons, I can't just use the Checks spreadsheets from each quarter. I tried to use Index/match but kept getting my formula wrong.
View Replies!
View Related
INDEX MATCH Two Criterion, AND/OR
This INDEX formula looks at two data sets for a match in two cells on the same row If there is a match betwen BOTH B&D columns on the same row, the code will take data from column F of the second data set and put it in the matching row of the first data set How do I set this code to do an OR operation rather than an AND operation on the match check? I.e. a match in either B OR D rather than B AND D? =INDEX(custom!$F$2:$F$99,MATCH(1,(custom!$B$2:$B$99=B1)*(custom!$D$2:$D$99=D1),0)) Thanks in advance
View Replies!
View Related
Index, Match For VBA
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 Replies!
View Related
Index And Match Funtions
I am using this formula INDEX(TS!$A:$A,MATCH(A$8,TS!$F:$F,0) The resulting name that comes up I need to arrange it in order according to what is in column CM. I wanted to use the large function but am unsure.
View Replies!
View Related
INDEX / MATCH Giving #value
Sheet 1 has 3 columns: grade, step, NBS Sheet 2 has 3 columns: SM, grade, step I want to add a column NBS to sheet 2 for that exact grade and step, from the data on sheet 1. I tried the following formula but it gives me #value!. =INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$A$6&sheet1!$B$2:$B$6,0),3)
View Replies!
View Related
Index Match The Maximum Value
=INDEX(A1:F2,MATCH(MAX(A2:F2),A2:F2,0),1) The desriptions are in a row in cells A through F The values are in the cells below I want to find the max value and return the description above. I come up with the formula above, which is not giving me the correct answer. I get the answer of 5 as shown below. ABCDEF 587135 5
View Replies!
View Related
Vlookup OR Index And Match.
I have a worksheet that has numerical data in A1:A22. In E1:E61 I have set values in numerical order and in F1:F61 I have a letter or letters that correspond to E1:61. I am trying to write a formula in B1 that will look at the value in A1 and then match it up in E1:E61 and then return the corresponding letter in F1:F61. e.g: in A1 i have an INDEX and MATCH formula
View Replies!
View Related
Index Match Several Returns.
agentsIDCount Name1ACCT1Name2ACCT2 Joe1113 Jimmy2222 Sarah3333 The above is A1 thru G1. I have a list of accounts on Sheet2 that have the agent ID's (listed on K:K), Acct #s listed D:D and names E:E. I want to match Sheet1 B2 with Sheet2 K$1$:K$50000$. I want to return the first match under this ID from Sheet2 Column E (to post on 1 Name on sheet 1) and then Column D (to post on 1 Acct# on sheet 1). Then the next match goes to 2 name, 2 acct # and so on.
View Replies!
View Related
Index Vlookup Match
I have a tab that has Employee Name in one column and the benefit they chose in another column. The employee name is repeated a number of times since they have chosen more than one benefit. I am trying to create a new tab where the employee name in is the first column (only once) and each benefit is listed in separate columns along the top. I want to put an "x" in the column for the benefit the employee has chosen. Is there a way to say "look for this employee, see if this benefit is what they chose, and if it is, put an "x", if not, leave blank"?
View Replies!
View Related
Index, Match For Highest Value
1) I need to show the latest PM service date. 2) I need to show the latest "other" repair type service date ( which could be GM, RE, **, or WY type). I just need the latest date for any of those types. 3) Finally, I need the latest Meter reading, regardless of type. In my "Master" sheet, so far I've only accomplished finding the first work service date and the first meter reading listed for a piece of equipment. I don't know how to go from there. Note: Some equipment will not have any work orders originally, and some equipment may have a service date but no meter reading because the equipment has no meter to read. I've colorcoded the columns I'm using as references in my calculations in the attachment.
View Replies!
View Related
Index, Match In Table
I have a table with the months from B1:M1 & Customers from A3:A35. B3:B35 are transaction numbers. On another sheet, I have a cell, C2 with the name of the month that I type in, I've named it MONTH. I also have a seperate section for each customer that NBVC has helped me populate from another sheet based on MONTH and their actual transactions. At the bottom of the section, I want it to populate from this other table based on the month in the MONTH cell.
View Replies!
View Related
