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 have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2 i want to do this with every row from row 2 to 55
View 9 Replies View RelatedI 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 RelatedI have 4 grids on the trending tab. I want to find the top 10 highest Color assets from Fleet Volume -Color tab under the correct month.
So for example, if the month is May as in cell G3 on the trending tab, then look at the data under the month of May on the Fleet Volume-Color tab and find the top 10 assets and drop in the City, Address, Model, Serial Number and then volume to the 1st grid on the trending tab, then repeat for Highest B&W
I want to the do the same for each of the other 3 grids on this tab. I want this to update based on the month on both tabs.
Of course, the data on the Fleet Volume tabs is a small range due to size, the data is a lot larger.
Is there are way to do this with a formula? I tried Large and small formula but not too sure how to bring over the other data like City, Model etc.
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))
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?
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.
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.
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?
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.
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?
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.
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.
Here'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.
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 Replies View Relatedi 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 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.
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
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'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 Related