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
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
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
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
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
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
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/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
VBA Index & Match
Sheets("Project").Cells(Application.WorksheetFunction.VLookup(Val(ComboBox2.Value), Worksheets("Project").Range("A:AZ"), 5, False)).Value = CDate(TextBox4.Value) What i'm trying to do is when i make a change to a textbox (in this case textbox4), I want to write over the cell that it relates to. (combobox2 contains the value that matches within column A  so that particular row) For eg, this particular textbox should reference column 'E', and then the row that matches the value in combobox2..... i was trying to use a vlookup, but it didn't work (kept putting ym new value into cell IE155 lol)
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
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
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 the below formula which works fine when I only want it to lookup one sheet but I am now trying to get it to lookup 2 sheets. I have tried numerous variations but still no luck. Plus I am not sure that with the formula that I am using it is even possible. HTML =INDEX('2008'!$B$2:$J$1697;MATCH($B6;'2008'!$B$2:$B$1697;0);2)+INDEX('2007'!$B$2:$J$1697;MATCH($B6;'2007'!$B$2:$B$1697;0);2)
View Replies!
View Related
VBA Match And Index
I have a sction of code that is telling me it cant find a match when I am pretty sure there is. 'Update todays Sales with stock With ActiveSheet Set StockboardMastercodeRng = .Range("A8:A1000") End With With Worksheets("SALESDATA") Set SalesMastercodeRng = .Range("B2:B200") End With For Each SalesMastercode In SalesMastercodeRng.Cells res = Application.Match(SalesMastercode.Value, StockboardMastercodeRng, 0) With Worksheets mbUID = StockboardMastercodeRng.Value End With If IsError(res) Then 'FAILS HERE 'no match Else StockboardMastercodeRng(res).Offset(3, ColumnNumberOfActiveDate + 2).Value = SalesMastercode.Offset(0, 6).Value End If Next SalesMastercode
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
#N/A! Index & Match
I have a formula that performs an INDEX  MATCH on a table of data based on two input cells. If the inputs are outside the bounds of the data table the result is blank. The data tables size and contents are static. The inputs used to perform the INDEX and MATCH are Dynamic Named Range. =INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))*100 Could this be easily solved by introducing and IF statement that if the resulting INDEX and MATCH is outside the bounds of the data table the result would appear as '0'(zero)?
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 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
