# Using INDEX & MATCH With MAX And LARGE

Mar 31, 2008
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 9 Replies
ADVERTISEMENT
Jul 14, 2007

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 2 Replies
View Related
Feb 10, 2014

I am looking for the largest TOTAL SCORE in each division, but need to display 3 placings. Current error: If the score is a duplicate, it is repeating that score with the same rider.ALSO - I need to add the 'PLUSES' as part of the tiebreaker (i.e. 940 with 24 pluses would be 1st, 940 with 4 pluses, 2nd...etc)

View 3 Replies
View Related
Jul 16, 2012

how to return the top 5 results in a set of data.

In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.

View 1 Replies
View Related
Sep 13, 2012

I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?

I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.

B1:

[Code]....

C1:

[Code]....

Warehouse S/S %

WH 1 50.00%

WH 2 57.14%

WH 3 0.00%

WH 4 50.00%

WH 5 100.00%

WH 6 60.00%

WH 7 33.33%

WH 8 66.67%

WH9 60.00%

WH 10 63.64%

WH 11 78.57%

WH 12 55.56%

WH 13 42.86%

WH 14 71.43%

WH 15 61.54%

WH 16 71.43%

Attached File: Book1.xlsxâ€Ž

View 9 Replies
View Related
Jul 2, 2013

I have a table of sales by month per account. Each account has an associated country, but there may be many duplicates of each country per month.

I want to create a formula which indexes the country field and gives me the country with the highest total sum for a specified date range. I'd like to be able to drag the formula down and have the country with the highest sales, then the second highest, and third highest, and so-on. Column ranges are specified below.

K - L - M - N

------------------------------------------------

Month

Year

CtryDsc

Rev

1

2011

[code].....

I was thinking of using an array function utilizing index, large, sumif, and match, but I am getting stuck. I'm honestly not sure if what I'm looking for is even possible, but in Excel, everything is possible, so there has to be a way!

View 4 Replies
View Related
Nov 12, 2013

I need a little coaching on big data. I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.

I have 1 Item Code.

I have a around a hundred invoices. (Sample 100)

I have a few hundred different customers. (Sample 100)

I have several hundred lines of billing data. (Sample 650)

I have several thousand lines of production data. (Sample 40,000)

In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.

In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.

Example

Prod System:

30 lines of data has a run amount and a total run amount for each line.

I am looking for tools to reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.

Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.

Invoice number

Billing item

Est. Quantity

Actual quantity

Billable Qty

Sales order

Position number

Sequence

Agreement

Billing source

Description

Total Billable Qty

[Code] ..........

View 4 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
Mar 24, 2009

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.

View 5 Replies
View Related
Aug 2, 2012

I have a large workbook in which I do some data analisys. One of the analysis I have to do is to figure out the top 20 oldest items. Therefore, I have the following formula in T55:

=LARGE(OpenTickets!N:N,1)

=LARGE(OpenTickets!N:N,2)

All the way down to 20

hen I need to match that number, say 534, to the person who has that old item related to them, so I have this formula on u55:

=INDEX(OpenTickets!A:A,MATCH(T55,OpenTickets!$N:$N,FALSE)) and so on

this worked great for a while, but I just came up with an issue. I have 2 items that are 534 days old so the formulas =LARGE(OpenTickets!N:N,1) and =LARGE(OpenTickets!N:N,2) correctly returned 534 as the 1 and 2 oldest items.

The problem came when the formula

=INDEX(OpenTickets!A:A,MATCH(T55,OpenTickets!$N:$N,FALSE)) and =INDEX(OpenTickets!A:A,MATCH(T56,OpenTickets!$N:$N,FALSE))

Matched the same employee name. The issue is that the formula looks only at the first instance where 534 occurs and then matches the name.

Instead I need to match both 534 to each employee name whose name appear next to 534. In other words my master workbook (Open Tickets) looks like this and must return:

534 Employee1

534 Employee2

My current formulas return:

534 Employee 1

534 Employee 1

View 4 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
Mar 10, 2008

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 9 Replies
View Related
Jul 5, 2008

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 9 Replies
View Related
Aug 26, 2008

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 9 Replies
View Related
Jun 9, 2009

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 9 Replies
View Related
Jun 24, 2009

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 9 Replies
View Related
Oct 23, 2009

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 9 Replies
View Related
Nov 23, 2009

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 9 Replies
View Related
Dec 22, 2006

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 5 Replies
View Related
Feb 20, 2011

I have tried using the 'arbitrary lookup' function as listed here but can't seem to get it to work. [URL] .....

I have a list of all football fixtures and results in the premier league this season and I have a separate worksheet with a drop down menu to load each team.

What I am struggling to do is to get a function that will obtain the data corresponding to each of the home fixtures and the away fixtures of the team selected in the drop down menu.

I have managed to get the first result but do not know how to get the 2nd occurrence, the 3rd etc.

My formula for the first is

=INDEX(Fixtures!$A$3:$R$380,MATCH($E$1,Fixtures!$C$3:$C$380, FALSE),5).

I've attached the spreadsheet. (Unfortunately the format of the excel sheet can't be changed)

Forum Example.xls

View 7 Replies
View Related
Feb 27, 2014

I'd like to use the INDEX & MATCH functions to look in a table (in 'misc') and find a value contained within a specific cell in Sheet 1 but I am getting a #REF error and I'm not sure why.The formula is as follows:

VB:

=INDEX(misc!1:50,(MATCH(Sheet1!A13,misc!D:D,0)))

Where:

Table ref = misc!1:50

Location of value to find = Sheet1!A13

Lookup array = D:D

View 1 Replies
View Related