See my attached spreadsheet. I am trying to get start dates and end dates based upon rates. The first start date is really simple, I just use A2. In F2 should return the last date from A2:A27 where the rate is I2. In E3 The I would like to return the first date in A2:A27 where the rate is G3. and so on. If the rate is 0 then the date can be blank or 1/1/1900.
View 2 Replieshow to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies View RelatedI am currently using the following formula and it gives the result P-10, P-10, P-11 below dates that are manually entered. I can not figure out why the date 10/31/09 returns a P-11 value when it matches a P-10 value? All other dates work expcept for the 12 period ending dates. Any help would be greatly appreciated.
=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0)
10/17/09 10/24/09 10/31/09
P-10 P-10 P-11
01/31/09 P-01
02/28/09 P-02
04/04/09 P-03
05/02/09 P-04
05/30/09 P-05
07/04/09 P-06
08/01/09 P-07
08/29/09 P-08
10/03/09 P-09
10/31/09 P-10
11/28/09 P-11
01/02/10 P-12
I have attached a sample workbook which shows my desired output.
In E3 I need a formula to index B3:B60 and bring back the value where D3 falls between the dates in A3:A60
I have manually typed 46 into E3 as 15/05/2013 is equal to or greater than 13/05/2013 but less than 20/05/2013
Attached File : index match between dates FF.xlsx
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.
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].....
INDEX/MATCH multiple ocurence match values needed
View 9 Replies View RelatedHere's my table:
2014 2014 2014 2013 2013 2013
Jan Feb Mar Jan Feb Mar
A 56 63 100 78 25 44
B 48 12 74 32 65 97
C 45 85 96 42 82 73
How will I use index match function to get the data that correspond to each criteria? For example I am looking for the value of A in Mar 2013.
i have over 1000 line invoice # with multiple listings....however they have different dollar values for each item
702-0195660 = $10
702-0195660 = $12
702-0195660 = $15
i have an index match in my "missing worksheet" **see attached**
but that is looking up only the first value
702-0195660 = $10
702-0195660 = $10 (should be $12)
702-0195660 = $10 (should be $15)
etc
i have attached the sheet..ive been on the past day!
attached file link below [URL]...
I'm running a College Football Bowl Game pool. NO money just fun! I've just about got this down except that my Standings area for this years members is not working like I want. There are 24 members and I want them ranked 1-24. This works however, when 2 or more members have the same score....only the first name in the column with that score appears.
Vea, Ray WA955
Braham, LukeHI916
Braham, LukeHI917
Bowers, LarryGA908
This cell comes back with 3 different dupes:
=INDEX(H$1:CB$1,1,MATCH(D87,H$83:CB$83,0)-1)
H1:CB1 are the Names
H83:CB83 are the points for each name
D87 comes this cell : =LARGE(H$83:CB$83,E87)
How can I tell Excel to show all 24 names including the dupes?
I am trying to retrieve values with INDEX and MATCH. I have column B that contains "i", and "m".
I have formulas for the first occurence of "m"
=(INDEX(NUP!$J:$J,MATCH("m",NUP!$B:$B,0)))
Which will look for the first "m" and output the value in column J that aligns with the "m"
How would I alter this or a new formula for the nth occurrence of "m"?
I'm trying to do in Excel 2000 that seemed way easier before I tried to write the formula. Any thoughts would be much appreciated.
I have some data setup more or less like this:
1State DeptJan Feb Mar
2California 50128 31 6
3Utah 20119 3 11
4California 20122 49 22
5New York 301 29 51 15
I'm looking to write a formula to retrieve the numerical value located in the cell for a particular month, across from a particular department number and state - so I can plug it into another spreadsheet.
I tried the following formula for January, but I get an "N/A" error when I hit 'Ctrl+Shift+Enter' to use it...
=INDEX(C2:C5,MATCH(1,("Utah"=A2:A5)*("201"=B2:B5),0))
Can you not use static values in this type of formula?
I have created a sales pipeline list in a workbook. One of the sheets ("Contacted potentials") has the following setup:
A---------------------B---------------C----------------D
Company name--------Location--------Probability--------Total company sales
Rows 1, 2 and 3 have other information. The actual list starts at the 4th row.
In another worksheet ("Overview"), I want to create a list of companies that have agreed on working with us (displayed by a probability of 100%). This should preferably be done in column A. Furthermore, column B should list the total company sales for that specific company. I have tried this myself using INDEX and MATCH formulas, by using the following formula:
=INDEX('Contacted potentials'!$A$4:$A$1000,MATCH(100%,'Contacted potentials'!$C$4:$C$1000,0))
But this only displays one of the companies, not all those which have a probability of 100%.
Ps. it is possible to automatically update this list (by pressing F9) when a company's status changes to 100%?
In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?
View 4 Replies View Relatedmaking a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results
View 6 Replies View RelatedI have a list of data that I want to average the number in column B when there is a match in column A
COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500
What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.
I receive data (A1:E2) which is sorted alphabetically accordng to name:
AaronBobConDanEd
3-Jan-135-Jan-133-Jan-131-Jan-132-Jan-13
I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:
1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:
1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
DanEdAaronAaronBob
The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".
How to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.
The formulas always assume the data is in multiple rows rather than multiple columns.
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.
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
I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed
View 9 Replies View RelatedI am trying to do an INDEX/MATCH to return a value based on multiple lookup_values.
The workbook is attached, and what I am trying to accomplish is this:
If 50, 75, or 90 are selected from Column D drop down of this (Main) worksheet, then return Column A, B, and C values for those rows to the Report worksheet.
So in my sample spreadsheet, John, Jim, and Jack's information should be returned on the Report worksheet because their Column D shows 50, 75, and 90. Alice, Wanda, and Cindy's information should not be returned on the Report worksheet because their column D shows Budget Only, Contacting, and 100.
My current INDEX/MATCH formula is =INDEX(Main!$C2,MATCH(50,Main!$D2),0), but this only works when column D has 50 chosen because I have only asked MATCH to return info if it finds something with 50 because I do not know how to ask it to search for multiple lookup_values.
So, as you will see, only John's information is returned on the report worksheet, but Jim and Jack's are not.
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
I'm using this formula to lookup data based on the lowest average.
[Code] .....
The problem is that the minimum average is a two way tie, and I need to return both data sets. It just happens to have the same average.
I've tried using SMALL(array,1) and SMALL(array,2) in place of MIN above, but it gives me the same data set. I've also tried resizing using offset.
See attached : sample.xls
Basically, I am trying to write a program that will index match through a range and if it doesn't find the value that it is matching to, it will add the value to the bottom of the range it is indexing against. In other words:
My C column has a list of CUSIPS. In column J, I would like for the User to be able to add a list of cusips and then have the ones that don't already exist in the list of CUSIPS in column C be added at the bottom of the range. I have written a macro now that uses the index match, but for some reason it adds all of the cusips that exist in J range to column C, not just the ones that are missing. I can provide a template if necessary.
Sub Filler()
Dim Row As Variant
Dim NumberOfRows As Long
Row = 0
[Code]...
Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name
means if values are A=1 B=2 C=3 D=4 E=2
it has to show like this A B E C D
But the result is coming like this A B B C D
Formula which i used
=OFFSET(B$6,MATCH(SMALL(C$6:C$18,ROW()-ROW(C$6)+1),C$6:C$18,0)-1,0)test.xls
Here i am attaching the sample file for your reference
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,
INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies View RelatedI have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View Relatedsorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.