Index / Match / Rank Combination
Jan 7, 2009
I have several months worth of data, lets say January to December in cells A1:L1. In the rows underneath I have data, however, it is several "sets" of data. For example, I have data in A1:L10 (the first batch) and another batch in A21:L30 and so on. So you can see that there are rows between each batch of data (this must remain thus).
What I would like to do is set up a formula that reads along the the dates at the top, then reads down to the batches of information only, and then ranks them.
Ultimately, what I want is to tap in say "September" and then in a table I want to have the top 10 ranked in order from the batches of information from the September column, and this will change according to the month I tap in etc.
I think it may be some combination of Index / Match & Rank, but I am struggling with the Rank formula applied to non-consecutive ranges!
View 9 Replies
ADVERTISEMENT
Apr 8, 2009
I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.
View 4 Replies
View Related
Aug 14, 2014
What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.
(some examples from the file)
NR2 NR DATE
100707987121951
100702347121960
100707750121960
100707721121960
100702422121960
[code]....
So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.
I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)
View 3 Replies
View Related
Sep 22, 2009
I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.
The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.
View 3 Replies
View Related
Nov 6, 2008
My sheet has a bunch of Name's in column D, either the text "Store" or nothing in column E, and a dollar value in column F. I'm trying to figure a way to display the names in order of highest to lowest dollar value based on the criteria that the name must have the text "Store" next to it in column E. If column E is blank then the name would not be included in the ranking. I thought I could use the following code to accomplish this:
View 13 Replies
View Related
Sep 21, 2009
I am trying to rank stores based on a quantity of run outs they have. I have a list of stores in order with the run out quantities. I use the large function to figure out the rank but when you have two stores with the same quantity it only finds the first on in the list.
View 2 Replies
View Related
Dec 6, 2013
I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is:
=RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1
To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there.
A source below really shows the formula should work. [URL] .......
View 2 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
May 3, 2012
I want to convert a Lexicographic Index Number, better known as Combination Sequence Number (CSN) to a combination using an EXCEL formula.C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.
I have a lookup table in cell M1:R56 which holds the correct data, that I am pretty sure about.
In cell G1 I have the Lexicographic Index Number I want converted to a combination.
In cell J2 I have the value 39 (n).
In cell I2 I have the value 5 (k).
In cell K2 I have the formula =COMBIN($J$2,$I2).
In cell A1 (the first number in the combination) I have the formula:
=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1,$M$1:$R$56,6))
In cell B1 (the second number in the combination) I have the formula:
=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,5))
In cell C1 (the third number in the combination) I have the formula:
=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,4))
In cell D1 (the fourth number in the combination) I have the formula:
=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,3))
In cell E1 (the fifth number in the combination) I have the formula:
=IF(G1=0,"",$J$2-($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,1)))
This works for the vast majority, but gives an ERROR when numbers 35,36,37,38,39 are in the combination, but funny enough NOT all the time.Lexicographic Index Number 575757 which is the maximum combination gives ALL ERRORS.
View 9 Replies
View Related
Dec 22, 2009
Have a series of tables that are put in order ascending and desending using rank and index. the problem is the last 3 tables. short calls ranking and hang ups which puts #n/a instead of the persons name. long calls ranking which doesnt put them in any sort of order at all.
View 4 Replies
View Related
Apr 6, 2009
Can Offset be combined in an Index Match Match formula as per the attached sample?
View 4 Replies
View Related
Apr 24, 2014
How to use offset in combination with match and vlookup. Well I think I have to use Offset to find the value ( cell with time in it).
I have in my workbook 3 sheets: Sheet1, Sheet2 and Agents.
In 'Sheet2' every week I upload a report with persons and every person has a certain amount of time behind their name.
In 'Sheet1' I want to get (load) the data: the person and time from 'Sheet2'.
In 'Agents' I only match the names. That's because the names in the report I upload in 'Sheet2' have a different lay-out then the ones I use.
The matching and to get the names correct in 'Sheet1' Is no problem. Though I get stuck with the cells where the time is placed in the report I upload in 'Sheet2'.
The persons are in Column C ( C7, C26, C45, C64 etc) but the value I also need to get is not in line behind the names. It's In the 7th row under the name and in Column L.
Example:
Wiebe (C7) time ( L14)
Gary (C26 time ( L33)
Kay (C45) time ( L52)
What I use to match the names and get data is this formula.
=INDEX(Sheet2!$A:$L;MATCH(VLOOKUP(Sheet1!$A2;Agents!$A:$E;5;0);Sheet2!$C:$C;0);MATCH(B$1;Sheet2!$1:$1;0))
Is it possible to use Offset ( or something different) in this formula to also get the cells with the time ( matching with the right person)
View 14 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
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
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
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
