# Return MAXIMUM Based On Criteria

Aug 13, 2008

i have a spreadsheet like the following

A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan

and so on...

So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?

ADVERTISEMENT

## How To Search Dataset And Return Maximum Value Based On 3 Using (array)

Dec 6, 2012

I want to search a data set and return the maximum value based on 3 criteria using (array) formulas.

Say the data set contained the following:

Row 1: column headings (date, name, data 1, data 2, data 3, etc)
Column A: dates
Column B: names
Columns C through G: data

I want to find the maximum value for a given name, in a certain month, and a nominated data column (e.g. data 2). For example:

Month = June
Name = Geoff
Data column = Data 2
Max value = ????

All of the data in the data columns is numeric.

## Return Values In Range Based Upon Relationship To Maximum Value

Jan 22, 2008

I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.

The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.

## Identify/highlight Maximum Value Based On Multiple Criteria

Dec 4, 2009

I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:

INDV DATE TIME FIX ....other attributes

INDV = unique code identifying the individual

The data is sorted by INDV, then by DATE, then by TIME.

I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.

## SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??

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?

## Return A Value Based On Criteria

Jul 3, 2014

In range B1:C5, I want to return the value in column B to column A if column C is empty as shown in the following table.

Coml A Coml B Coml C
S1S1
S3S2 89
S4S3
S4
S5 67

## Function To Return Value Based On Two Criteria?

Aug 10, 2014

I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.

In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.

Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "\$240.00".

I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.

## Return Data Based On Criteria?

Dec 19, 2012

generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.

1
2 JLKNSTTP
3 85934942
4
5

For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.

=if((3:3>8),offset(A3,-1,1,1))

## Return Text Based On Criteria

Jun 2, 2009

Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.

## Return A Text Value Based On Criteria

Jan 26, 2010

I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.

Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.

## Return List Based On Criteria

Dec 7, 2011

I have a table with the following headers: Customers, Location, bill number, date of bill, number of days you have untill you must pay the bill, the rest are not important

I need to return a list of the bills from the last six months, in which the customer has been granted with days until he must pay the bill(there are some with no granted days).

The table headers are translated, they are not so long.

I need to do this until friday.

## Return Value Based On Range Of Criteria?

Sep 25, 2013

I would like Excel to return me a value based on many criterias in the same worksheet.

E.g

A
B
C

[Code].....

QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.

## Return Result Based On Criteria

Oct 27, 2006

if Cell F42 contains 50%, then my fomula returns "wrong"

=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))

how do I get the formula calculate correctly?

## Return Column Values Based On Criteria?

Jan 31, 2014

I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45

The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.

I need a formula that would look at my source data, and populate a sheet in the following layout

agent_id, agent_name, title, start_time, end_time

The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30

If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.

## Return List Based On Multiple Criteria

Mar 13, 2014

I have a spreadsheet with several hundred line items that I basically want a summary of.

STRINGS CRITERIA OTHER
string1 criteria1 other2 <---disregard because of other2
string1 criteria2 other1
string1 criteria2 other1 <---disregard because it is a duplicate
string2 criteria3 other1
string2 criteria3 other1 <---disregard because it is a duplicate
string2 criteria2 other2 <---disregard because of other2
string3 criteria1 other1
string3 criteria1 other1 <---disregard because it is a duplicate

I need the formula to return the value in STRINGS & CRITERIA, but I do not want it listing duplicates.

-I need the summary list on a separate sheet
-STRINGS, CRITERIA, and OTHER are all strings
-STRINGS are in alphabetic order
-CRITERIA are listed in groups with regard to their STRING, but not necessarily alpha-order
-OTHER are not in any particular order, but there are only two choices for OTHER
-OTHER: if OTHER=other2, it should not be listed in the results

RESULT

STRINGS CRITERIA
string1 criteria2
string2 criteria3
string3 criteria1

## Return Result Based On Multiple Criteria

Sep 19, 2009

I have been trying this for hours but to no avail.

I have a table with 4 columns headers
Name, Amount, Loc and Code

The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB

I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if

1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same

I have attached a sample to illustrates the result

## Return Data To Another Sheet Based On Criteria

Jul 28, 2006

Sheet 1
Column A = Dates
Row 1 = Filenames

I enter a Y in the intersecting cells of the Filenames & Dates to show which files were downloaded.

I need is a formula that will rearrange this layout onto Sheet 2 so that each ‘Y’ (downloaded) filename is inserted in a cell corresponding to the Date.

(see attached sample)

## Return Data Based On Lookup Criteria

Aug 31, 2006

I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.

For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..

## Return Multiple Results Based On Criteria

Dec 1, 2006

Please see the attached sheets.

Here's the issue:

On sheet "master" I have a list of goals in column A.

Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.

I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.

I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.

Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.

how this done for AL, I can go ahead and create the other sheets.

## Return Latest Date Based On Matching Criteria

Jul 25, 2014

I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.

=MAX(INDEX(\$A\$1:\$C\$40,MATCH(A2,\$A\$1:\$C\$40,0),3),1)

MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14

## Match 2 Criteria And Search Column And Return Value Based On Value

Feb 14, 2014

I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:

If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.

2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE

## Array Formula To Return Multiple Values Based On Various Criteria?

Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.

## Search Text String And Return Value Based On Multiple Criteria?

Apr 8, 2013

Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?

E3a7qta.png

## Return Multiple Values Across Columns And Rows Based On Criteria

Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.

EG

Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50

## Formula/Function To Return Multiple Values Based On Criteria

Aug 25, 2006

I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

## Query Worksheet & Return Rows Based On Conditions/Criteria

Jun 1, 2008

I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.

1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.

2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.

## Excel 2010 :: Return Positive Or Negative Value Farthest From Zero Based On Criteria From Another Range

Mar 11, 2014

I am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range:
{=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}

However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?

PRB
Y
Z
A90B90
A90B-90

[Code].....

## Lookup Maximum Value & Return Corresponding Row

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.

## Return Text Associated With Maximum Value

Jan 8, 2008

If I have names in one column and amounts in the next column, how do I determine the maximum of the amounts and return the associated name from the other column?

## Maximum Value Using Criteria?

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.

Copyrights 2005-15 www.BigResource.com, All rights reserved