# Find Three Largest Numbers From A Variable Starting Index Match Point

Sep 1, 2013
My data is set out in columns, where alternate columns provide day numbers for given years (we can call these type 1 columns), with adjacent columns containing values which correspond to those type 1 column day numbers (we can call these type 2 columns). There are about a hundred columns in total (50 of each type). I would like to get excel to return the three largest numbers within each type 2 column, but I want to exclude data within the type 2 column above the point which is adjacent to a specific (varying) day number in the type 1 column. The location of this point varies for all the type 1 columns, according to a third row of numbers (the look up start point), which are currently listed below the dataset in every type 1 column. So, for the type 1 column "year 1", I would want Excel to ignore the values 0 and 1, which are listed next to day numbers 78 and 79, and begin looking for the three largest values down the column starting from the value which is adjacent to 81 (which is a 2). In type 1 column "year 2", excel would start looking for the largest values from the cell adjacent to 78, so it would ignore the 18 at the top of the column,and would return 2 and 12. And so on.

Year1

Value

Year2

Value

Year3

[Code]...

look up startpoint

81

78

62

83

View 9 Replies
ADVERTISEMENT
Oct 20, 2009

Let's say I have a list in worksheet 1. It's in column A, starts in row 1 and goes.

Red

Orange

Yellow

Green

Blue

Purple

In worksheet 2 I want to list in row 1, starting with column A, which colors have a value next to them. I want the list to match the first worksheet's order. I'm looking for a formula solution.

Example 1

WS 1

Red 3

Orange 4

Yellow 5

Green

Blue 1

Purple..........

View 4 Replies
View Related
Mar 14, 2007

I am trying to create a macro that works with a range of cells and identify the five largest numbers in the range. The following are the steps.

1. I select a range of cells and run the marco.

2. The macro will identify the largest number in the selection and make the cell color red.

3. Then it will find the cell with next largest number and turn the cell orange...and so on till the 5th largest number.

View 9 Replies
View Related
Jan 5, 2010

I created an Index/Match array forumula in the worksheet. It works.

View 2 Replies
View Related
Jul 23, 2014

I'm trying to perform an index / match lookup that can have a dynamic indexed column.

Sheet1 has the master table and sheet2 is where I'm putting the formula. The table on sheet1 has a RecordID field that uniquely identifies each record and that must be typed into Sheet2 [RecordID] column and then matched to sheet one to identify the record

Code:

MATCH([RecordID],Table1[RecordID],0)

The column headers on Sheet1 are used as data validation for the column [Field] on sheet2. The value of the Field column on sheet two should determine the the column on sheet1 to index.

This is what I have so far:

Code:

=INDEX(MATCH([Field],Sheet1!A:AQ),MATCH([RecordID],Table1[RecordID],0))

View 7 Replies
View Related
Sep 3, 2009

I would imagine that the title of this thread made little sense, so let me try to explain:

Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.

So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.

View 9 Replies
View Related
Dec 10, 2008

I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.

Option Explicit

Sub PointName()

Dim Ws As Worksheet

Dim Rng As Range, Cel As Range

Set Ws = ActiveSheet

Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))

For Each Cel In Rng

Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "

Ws.Columns("A:B").Copy

Sheets.Add

With ActiveSheet

.Paste

.Name = Trim(Cel)

.Range("A1").Select

End With

Next

Ws.Activate

End Sub

I turned it off for a while and when I turned it back on I am getting an error

Unable to set the _Default property of the PivotItem class

Debugger is highlighting

Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "

View 9 Replies
View Related
Nov 24, 2009

I am using the following code to identify any blank rows in a sheet and delete them:

View 3 Replies
View Related
May 16, 2014

I am attaching an example of what I am trying to do.

sheet 1 is how the jobs are assigned - Each job is assigned based on the part type and the last 2 digits of the order number. For example, Jon works any order that needs wheels and the last 2 digits of the order number are between 00 and 49.

sheet 2 is current jobs - this sheet shows the current job by order number and part, the employee column (C) is empty and is where I would like the formula and result

sheet 3 is the result I am looking for - formula results are in yellow.

So what I am trying to do is make it so that as employees come and go and assignment of jobs change on sheet 1, sheet 2 will automatically update the employee name to match.

View 4 Replies
View Related
May 22, 2014

Is there any way to set the list from a Combo Box to automatically start in the middle?

Whenever I select a Combo Box, the drop down list always automatically starts at the top of my selected range, ideally I would like it to start in the middle.

For example my Range that I am filling the Combo Box with lists dates: Today()-7 to Today()+7.

At the moment the first item in the combo box list is Today()-7, I would like it to be Today().

View 2 Replies
View Related
Nov 3, 2008

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

View 9 Replies
View Related
Dec 16, 2013

I have a standard table with quite a few rows. Columns I have are :

Text | Lower number | Higher number

What I would like to do is, in another worksheet, enter a number and I'd like to go through the table and if the entered number is in the range of the Lower and Higher number columns, have the Text displayed.

Using Index Match I can do this if the number matches Lower or Higher number but not if it's between them.

View 4 Replies
View Related
Jul 1, 2014

I have a data table, called Table1 in Sheet1. In a simple form it is

Customer ID

Comment

A

asdf

B

jkl;

So in VBA, I need to figure a way to give me the cell address of the matching cell in Comments. So say Customer ID starts at A1 ... that means the Comment for Customer A is B2.

How would I get that reference in VBA?

View 2 Replies
View Related
Jun 25, 2014

Cell P14 = 4.7

My array is H42:N72, in H42:H72 I have numbers 15,14,13....0,-1,-2,-3, etc...

In N42:N72 there is a corresponding $ amount that I would like to return based on finding the value which is greater than 4.7 (so, looking to match the $$ amount to the number 5 in column H42:H72)

I've tried using INDEX MATCH but can't figure this out.

View 10 Replies
View Related
Mar 28, 2012

I am struggling with a INDEX,MATCH and MAX formula. I am trying to retrieve values from column E with two criteria:

1. That the value in column C is equal to a lookup value

2. That the date in column A is the closest before date for a lookup date

I have attached an example file.

View 5 Replies
View Related
Nov 12, 2008

Is it possible to use Match to find a value within a Named range and then, based on that value, use Match and Index again to find a value two columns across?

I have a list of clients in Column A, with 10 cells between each. In Column B, I have a list of currencies (the same currencies next to each client) and in Column C the rate this client pays for this currency. I want to reference these rates from an external workbook. Is there any way to use Match to locate the client name, then use Index/Match to locate the rate for a particular currency, somehow telling Excel where to look the second time?

View 10 Replies
View Related
Mar 13, 2013

I'm using Index/Match to find a value in another workbook, but there is one value that I want to ignore. For example here is my function --

=INDEX(ZZANALYSIS_PATTERN.xls!$A:$H,MATCH(D8,ZZANALYSIS_PATTERN.xls!$G:$G,0),2)

In D8, the value is Blue. Here is an example with made-up values (X is meaningless values) --

A B C D E F G

X Apple X X X X Blue

X Pie X X X X Blue

When I use the function, I want it to retrieve Pie, but it always retrieves Apple. Is there a way to ignore Apple? I've tried throwing "Apple" in there a couple different ways, but nothing is working. Or, if I can search from the bottom up, that would work as well.

View 9 Replies
View Related
Apr 22, 2014

I've used the below formula to fill column D with the name of the cheapest supplier for the parts listed in each row.

=INDEX($G$1:$M$1,MATCH(MIN(G3:M3),$G3:$M3,0))

Is there a way to fill columns E & F with the 2nd & 3rd cheapest suppliers?

My table is shown below:

Capture.PNG

View 2 Replies
View Related
Sep 30, 2009

I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX

I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is:

=MATCH(1,INDEX(--(B2:B883>=30),0),0)

Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?

View 6 Replies
View Related
Apr 11, 2009

This had been hunting me for weeks and I still could not come over it after weeks of study the conditional formating IF, match, index, Vlookup, etc to find several duplicated value, e.g Column B = Name 1 & Row B = Time and so on, where as Name will have data of Name 1 duplicated in different time, while row B, "Time"would change to a different Name and maybe it would go back to Name one.

My problem was dont know which formula I can use in a different sheet to bring back the data to Name 1 did Task A at Time A, while Name 1 did Task B at Time K, etc. If this is not Name 1, then Name B replace above.

View 4 Replies
View Related
Jun 21, 2012

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.

Code:

IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",

INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))

I tried using G$3-1 but this doesn't appear to work.

View 1 Replies
View Related
Nov 23, 2012

I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.

I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004

I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.

'Daily Entry Sheet'!$B$8:$B$138 = Date

'Daily Entry Sheet'!$F$8:$F$138 = Property #

'Daily Entry Sheet'!$G$8:$G$138 = Property Location

'Daily Entry Sheet'!$H$8:$H$138 = Work Order Number

Following is what I presently have coded.

The following Works but is not completely as I need.

=IF(OR($F8=0,$F8="",ISNA(G8),$H7="Work Order #"),"",IF(ISERROR(INDEX($H7:H$8,MATCH($F8,Prop.,0))),"",INDEX($H7:H$8,MATCH($F8,Prop.,0))))

This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.

I looked for a function similar to the sumif or countif

=SUMIF(Prop.,$F4,WorkOrdNum)

=SUMIF(Date,"="&$V6,Total_____Hours)

But I don't need to add the work order numbers.

Countif can give me how many instances property # 919 is used,

But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.

View 9 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 17, 2008

I'm wanting a function to display the largest number starting with 7,8 and 9.

I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.

I need:

One displaying the largest number in the group starting with 7.

One displaying the largest number in the group starting with 8.

And one displaying the largest number in the group starting with 9.

Could I make use of an IF function and the MAX function?

Something like:

M20:

=IF("1st digit = 7", "display the largest of the list")

M21:

=IF("1st digit = 8", "display the largest of the list")

M22:

=IF("1st digit = 9", "display the largest of the list")

I've used =MAX(H5:H75), but this always displays the top "9" number.

I want something so I can show the top 8xxx number, and the top 7xxx number too.

View 9 Replies
View Related
Jun 19, 2014

I have a monthly report. Worksheets for each month, each row is a type of fruit (column A), and the number sold (column B). A new worksheet is added each month with the information. I also have a Summary worksheet - running total of all months: Type of Fruit (column A), Number sold (column B), and Total of all fruits - number sold. I would like a formula for the Summary worksheet - to match that fruit (column A) to any of the other worksheets - matching the fruit (column A), and bring back the number sold (column B). (note some apples are Fuji and some are Honey crisp - those would be totaled together)

ex:

Tab = Jan 14

Fuji apples3

Bananas2

grapes9

TOTAL14

[Code]....

View 2 Replies
View Related
Jun 21, 2009

I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.

View 2 Replies
View Related
Apr 9, 2014

I have 2 spreadsheets with many rows (in the tens of thousands) and columns (about 20 or so) of data.

I am trying to take the ID# from one spreadsheet and append it to the other, based on a name match. Typically I would use a vlookup, but, in this instance, most companies appear more than once and they often have many different ID#s associated with them. I am looking to return each unique ID# that is associated with each company, and display them horizontally next to that company's name.

Since this is including a lot of private data, I have included a small, generic example of what I am looking to search off of and output. Sample Spreadsheet.xlsx

I have seen examples of how to return multiple values using index functions, but, they all seem to only be able to handle one name at a time, and it displays the values vertically from that. Since I am trying to do this for thousands of rows of data, this won't work for me. I need to be able to have a function/formula of some sort that I can apply to each and every of the thousands of rows of data simultaneously. And, on top of that, I'd like to be able to display the 2nd, 3rd, etc ID#s for each company in additional columns of data.

View 3 Replies
View Related
Oct 29, 2012

I want to match a name to the second largest value of sheet.

The trouble is the top three valus are 125,113,113.

jason got 125

graham 113

james 113

when i do indexmatch formula it returns graham everytime as he is the first person to get 113 in the table. how do i create a formula to reurn

View 2 Replies
View Related
Mar 8, 2013

Code:

Application.ScreenUpdating = False

Dim c As String

Dim d As String

[Code]....

this works perfectly but I have found one problem, I want excel to find on the existing arrays "0,00" and "0", only full match and not only partial like for example:

-154.09, which triggers "0" for the second array and 120,00, that triggers "0,00" how to I change to only find full match with the existing arrays?

View 9 Replies
View Related
Jun 13, 2014

I have been trying to sum the largest 5 numbers that are in a row

the number are in the columns stating c1, e1, g1,i1,k1,m1,o1 ect for about and other 15-20 columns, so there is a missing, so a lot of numbers

thing to note i am missing a cell between the cells i need to count/ sum ...

View 8 Replies
View Related