Not posted for a few weeks, its been crazy at work!
I have a problem!
What I want to do is use the MAX formula in C:C, but return the value to the left of =MAX(C:C)
I’m not brilliant with Index which is what I suspect I need to use
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.
Help needed.xlsx
Assuming I have table with work weeks in the columns: WW01, WW02, etc.
And assuming I have few tools in a fabrique: A02, A08 etc.
Each tool have its own production value for each week: 12.15, 31.44, 17.98 etc.
I wish to built a function that will return me the best tool for a give WW with its value
To get the valus I used the functions:
=MAX(IF((F2:I2=D10),F3:I7))
=MIN(IF((F2:I2=D10),F3:I7))
And it worked okay (with ctrl-dhift-enter only)
To get the index I tried:
=INDEX(E3:E7,MATCH(MAX(IF((F2:I2=D10),F3:I7)),F3:I7,1))
=INDEX(E3:E7,MATCH(MIN(IF((F2:I2=D10),F3:I7)),F3:I7,1))
But the last argument must be an array. So I tried:
=INDEX(E3:E7,MATCH(MAX(IF((F2:I2=D10),F3:I7)),IF((F2:I2=D10),F3:I7),1))
=INDEX(E3:E7,MATCH(MIN(IF((F2:I2=D10),F3:I7)),IF((F2:I2=D10),F3:I7),1))
Still not working...
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))
I have the following data and i want to get the highest value over average using if, i wort this formula
=IF(INDEX(D3:D12,ROWS(D3:D3)>AVERAGE(D3:D12),"Greater","Lower"),
But it doesn't work.
The first sheet is called Sites and the second sheet is called Call Log-Jan-08
On the Call Log in cell A3 we put the division # and then we want the information to automatically fill out for the cells B3, C3 & D3. We want this information to come from the Sites list. In the sites list the information starts in A2 thru K2 (A1 thru K1 is the header info) and goes thru A645 thru K645.
I can't figure out how to get my Index formula to work.
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 RelatedI am creating a spreadsheet for the mortgage department. I have 2 tabs, appraisal fees & construction inspection fees.
Appraisal Fees will be completed by the processor as the applications are received and fees are collected. Column E is to identify loans that are construction loans, marked with a 'Y'.
In the construction tab, I want to bring over columns C & D from appraisal fees when there is a Y in column E of appraisal fees tab. I want to index this so that it starts from the top of the construction inspection fees.
Is it as simple as adding an index function to the start of the if formula?
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.
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 RelatedL26 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 RelatedI need to check when a cell is clicked and call a function name CalcValue.
However, I need the following:
1. Only cells in the square range from B3 to G8 call the CalcValue when clicked.
2. Determine the index of each cell in the following way:
B3 is (1,1) and G8 is (6,6)
So B4 is (1,2), B5 is (1,3), ..., C1 is (2,1), and so on
Should I do this with WorkSheet_Change and Target property?
I have been working a lot with VB.NET and C# but in VS and not in Excel.
Its only recently i ve got work with excel...Now straightaway coming to the matter i ve got some data in excel that needs to be modified. my data in excel sheet will be like this in one single column.
1)name
2)city
3)state
4)dealer
5
6
.
.
.
.
19)
and again history repeats itself
20)name
21)city
22)state./......................
I would like to have all sheets names in a column on the last sheet in my workbook like an index over the sheets.
View 5 Replies View RelatedIf 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.
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 Relatedinsert =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 RelatedI am looking for some assistance on RGB colours.
I have a spreadsheet with cells shaded in various colours and would like to count how many cells are shaded in the various colours.
I have tried to download the Ultimate utility suggested by DominicB in the postint "Counting Colours" but I do not have access to load software onto my computer :x
Without using a macro, is this possible?
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: ...
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)
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!
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)
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?
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.
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.
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
I have data essentially comprising a set of points with x and y coordinates. Is there a way to index and interpolate between points?
I can use INDEX or LOOKUP to look up a y value fo a valid x value but don't know a way to interpolate if the value is between x values. Is this possible?
in B2 want to have value of cell T1
in B6 want to have value of cell T2
in B11 want to have value of cell T3
in B16 want to have value of cell T4 etc etc..
so actually in every 4th cell in B row need to have formula which will show data from T row as in example above!
On sheet one you see a grid where for each person you can enter a value for a specific date. On sheet two you can see how we are able to find each date a person had a value of "P" entered. I need the file to do just a little more so I'm not even sure this is the best way to go anymore.
What I need, is for the data to appear as it does on Sheet3. Just two columns no formulas. I also would like a way where a quick click would show the dates where “V.’s were entered. Some problems / considerations: This is a mockup. The actual file I use contains Hundreds of names. Second, instead of looking for the values here on sheet1. I may need it to look for the values in a separate workbook on sheet1. I added c:lookhere[sch.xls] before the sheet1 reference in the formula
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)?
