Lookup Maximum Value
Jan 2, 2008
I have a column of merged cells which contains a number e.g. 396322669097172 and a time e.g. 00:00:00.
I can format this combined cell as a 'general', but think it can be formatted as any type of cell retaining this layout (after combination, prior I had to format both cells as textual), however when it is then not possible to get a maximum value from this column using a pivot table. The max comes back as zero.
I have tried using some freeware to combine the cells and I don't know what it did differently but I was able to order the values using a pivot table afterwards.
View 6 Replies
ADVERTISEMENT
Jun 9, 2007
There a a total of 13 sheets. Sheets 1-12 are named by months i.e. Jan, Feb, Mar, etc.. Each of these sheets contains a max temp for the day. There are cells at the bottom of each of these sheets that return the max, min, and average. The last sheet is an annual summary sheet. It collects info from the other 12 sheets and places them on it. There is a cell on the annual summary sheet that attemps (this is my problem) to determine the max and min temp for the year (easy enough). The problem is that once the sheet determines the max and min temp for the year to determine what day of what month it occurred on.
View 6 Replies
View Related
Dec 21, 2008
I need to look up the highest value in a column and then return a name in the same row as the value is found. I been playing with max and offset but they are not working for me.
View 2 Replies
View Related
Nov 21, 2006
i have a list of price list, and it is updated once a while, which means there is a change in version number.
The table is a direct extraction and it has to be like that...
I have tried to vlookup but only return 1 line, same as match and index... or maybe im not using the forumla correct.
Attached is the excel example hope this help. as you can see there is alot of dupe reference but different version and prices
I cannot concatenate the reference and version number as my lookup reference doesnt know what one is the latest version...
but one thing is constant, that is the version number is always needs to be the highest.
View 3 Replies
View Related
Nov 28, 2006
i have a matrix
index| a| b| c|
0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment)
I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View 2 Replies
View Related
May 15, 2008
How to lookup for greater than a given particular value in a list of data? Suppose the value to find is in cell C17 & the column to be looked for is F, in the attached sheet. Now the data range is from F8:L27. If the value in C17 is not present in Column L, then the value greater than the specifed value should be searched in Column L. I have tried using VLOOKUP, but cannot find for a range of values.
View 2 Replies
View Related
Nov 2, 2006
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View 4 Replies
View Related
Nov 10, 2009
in which the OP was trying to find the maximum date (Col B) in which the value in Col A occurred, DonkeyOte used this formula as a possible solution
View 4 Replies
View Related
Dec 11, 2008
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
View 6 Replies
View Related
Oct 23, 2008
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
View 4 Replies
View Related
Oct 9, 2008
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies
View Related
May 15, 2009
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
View 2 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
Apr 27, 2009
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
View 3 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Jan 26, 2010
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
View 4 Replies
View Related
Jan 20, 2009
How would i use a max and and if together? i have these scores with out or not out next to them and i want to know which score is the highest where the not out = yes?
View 4 Replies
View Related
Nov 7, 2008
In range A2:AAZ2 I have numbers but until the numbers are entered into, some cells in A2:AAZ2 shows #N/A.
I want to calculate the maximum number in the range A2:AAZ2.
My formula
=IF(MAX(A2:AAZ2)>0,MAX(A2:AAZ2),"") always returns #N/A, instead of the maximum number.
View 9 Replies
View Related
Dec 19, 2008
I am trying to generate a column that shows which organic suites are needed for each site. I have attached a sample of my spreadsheet.
It is difficult to explain what I am looking for, so I have just put some bullet points down.
Each suite required for each site
Only 1 of each suite to be counted for each site
This will show a total number of each suite needed
What I have so far seems like it is close, but it aways reads as 1 if something is in the row. I want it to read 1 if there is a 1 in either row for the same site and 0 if 1 is not present for either row for each site.
View 3 Replies
View Related
Feb 4, 2009
In a cell, I would like to limit the maximum upper limit. For instance, in a cell with (=H23*0.06), that value ranges from perhaps 1.48 up to 5.93; however, I would like for the upper limit to stop at 3.33. Would that be conditional formatting? How can I stop the upper limit at 3.33?
View 2 Replies
View Related
Jan 20, 2010
I am having an issue getting MAX to pull the largest number from a range of numbers calculate by formulas. The code I am using to figure these numbers is as follows:
View 4 Replies
View Related
May 19, 2014
I am working on the report where is have diffferent Inst Numbers (Column "C") and also Frame (Column "D").
Now i need to know the maximum Frames in (Column "F") for Instrument Number. I used below formulae but it takes much time get the output.
={max(if(C:C=C2,B:B))}
So i need easier way thru VBA Code where it automatically pulls the MAx Frames with the specified criteria.
View 11 Replies
View Related
Dec 20, 2008
In the attached excel sheet, i am trying to find conditional maximum of a set of numbers. In Cell C3, I have the formula =MAX(($S$4:$S$500=$W$4)*(M4:M500)) entered as array function and is working fine. In Cell J3, I have the similar formula MAX(($S$4:$S$500=$W$4)*(J4:J500)) entered as array function.. This is not working...always gives zero. The difference between Range (m4:m500) and (j4:j500) is that, m4:m500 are all positive numbers and j4:j500 are negative.
View 12 Replies
View Related
Jan 26, 2010
I am using the vlookup function for a reference that has two values. I want to choose the max. See attached file. In essence, I would like the vlookup for "a" to output "2" and "e" to output "7".
View 2 Replies
View Related
Oct 11, 2009
I wanted to know if there was a way to find the Maximum value given a number of conditions are satisfied.
Similar to COUNTIFS and AVERAGEIFS, where you state the data range to use, the condition's range, the condition, then the next condition's range, the next condition, etc....
View 9 Replies
View Related
Jul 9, 2006
I need a formula for Excel
I want column J2 to L2 to display the highest amount on colum M2 and add 2 to it.
I've tried some IF formula.
View 9 Replies
View Related
Oct 25, 2006
I have a userform that captures 12 textboxes worth of numbers. I want to find which box has the max value(two or more could be equal, would want that info).
I wanted to do this without putting the values back into cells to do this. Is there an easy way? Or would that be the optimal way to do this?
View 9 Replies
View Related
Dec 2, 2006
Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
View 2 Replies
View Related
