Tracker
HOME    TRACKER    Excel

# 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

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

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.

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.

Want To Display The Cell Next To A Max/large Number.
I'm working on a spread sheet to identify the to ten numbers from a list. I can find thes numbers and use ( =MAX('MHF2'!B:B) )for the function to do so. I'd like to dispaly the data in the cell directly to the left of this number... How do I do this?

11302
21000
3484
4440
5428
6419
7367
8333
9315
10258...........

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?

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)

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.

INDEX / MATCH Multiple Ocurence Match Values
INDEX/MATCH multiple ocurence match values needed

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)),"")))}

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.

Lookup/Index-Match-Match Using Segments
Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.

Adding A Third Match To An Index Match Formula
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:

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))

Using Match & Large To Rank Values.
I'm using a Large function embedded in a Match function to a ranking.

The issue I'm having is that if there are 2 of the same values in the Top 5, it counts the first one twice rather than each one once.

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/excel-dynamic-ranges-vba.htm

http://www.cpearson.com/excel/lookups.htm
http://www.cpearson.com/excel/lists.htm

and Microsoft's at

http://support.microsoft.com/default...;en-us;Q152407

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.

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!

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” & “Single-core 70°C thermoplastic non-arm Cu Table 4D1”

Index And Match The Value
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.

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

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.

Index Match Getting #N/A

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?

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

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.

SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

Would a Index/Match/MAX function be more efficient?

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.

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?

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?

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.

Index Find Next Match
i am trying to find all the 0 in column P and return col M from the same rows, this finds the 1st, how do i find the rest?

=INDEX(M\$3:M\$100,MATCH("0",P\$3:P\$100,0))

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

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.

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.

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 A-1

B1:I1 = A ---> H
A2:A9 = 8 ---> 1

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

Index With Match With Duplicate
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

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.

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"

Index/match To Get Single Value

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.

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))

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

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.

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)

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

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

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.

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

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 color-coded the columns I'm using as references in my calculations in the attachment.

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.