# Match Criteria And Return Lowest Numeric Value

Apr 3, 2006
I would like a Formula to match 3 criteria in the following order:

1. Numeric Reference (NOT Unique)

2. Numeric Values ( NOT Unique) - lowest value

3. Numeric Label (Unique)

The Numeric Reference that I'm looking for will vary (Input Cell).

Search /Match ALL specified (duplicate) References.

From the specified References Return the Numeric Label that has the "LOWEST"

Numeric Value .

Data Layout is 3 Rows:

1st Row E4:AC4 Numeric Values (NOT Unique)

2nd Row E5:AC5 Numeric Labels (Unique)

3rd Row E6:AC6 Numeric References (NOT Unique)

Sample Data:

E4:AC4 Numeric Values (NOT Unique) 145 127 120 160 130 170 160 160

E5:AC5 Numeric Labels (Unique) 20 21 22 23 24 25 26

27

E6:AC6 Numeric Reference 8 0 8 2

0 10 8 30

Scenario:

Looking for Numeric Reference 8, the relevant Numeric Labels are 20, 22 and

26. Their respective Numeric Values are 145, 120 and 160. The Numeric Label

with the lowest value of 120 is 22.

Expected Result:

Numeric Label 22

View 14 Replies
ADVERTISEMENT
Jan 11, 2009

Rather than attempt to describe my problem here and risk cofusing people on what I want to achieve I have put a diagram together. I think this is the best way to illustrate my problem.

Diagram is available here

[url]

There is also a copy of the document available here for any body willing to take a look.

[url]

Please bare in mind I need this doc to be compatible with the 2003 version of Excel.

View 14 Replies
View Related
Feb 22, 2007

I have 2 rows of data and want excel to find the number of times that a number appears in the first row and then return the value of a cell in the same column but in the second row of data. I need it to repeat this until all matches in row one, and their corresponding number in row 2 have been found and then add all the results from row 2 together to give a single numerical answer. I have tried the ' lookup' function but this only returns the first number that matches the criteria and does not continue to find the remaining matches.

View 7 Replies
View Related
Nov 6, 2013

I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.

I need INDEX/MATCH to return the correct values for the subset below.

Column A (Forecast Status) - Column B (Account Name)

Commit - Account01 (Row 1)

Commit - Account02 (Row 2)

Won - Account03 (Row 3)

Won - Account04 (Row 4)

Won - Account05 (Row 5)

Upside - Account06 (Row 6)

Lost - Account07 (Row 7)

Upside - Account08 (Row 8)

Won - Account09 (Row 9)

Commit - Account10 (Row 10)

In a new tab, I am creating a 'dashboard' view that will group each of the following:

Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)

Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)

Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)

Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)

View 1 Replies
View Related
Oct 26, 2011

I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.

=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))

Code reflects the ALL GRADES data below.

TERM SUBJECT NUMBER CRN CREDITS GRADE ID

1 Biology 301 12345 4 B 123456

2 Biology 302 23456 4 B 123456

3 Biology 303 34567 4 A 123456

3 Biology 338 65432 4 C+ 234567

3 Biology 338 54321 4 A 345678

1 Biology 336 43210 5 B+ 456789

1 Biology 337 43210 2 A 456789

1 Biology 338 65432 4 B+ 456789

1 Biology 301 12345 4 A 567890

2 Biology 302 23456 4 A 567890

3 Biology 303 34567 4 A 567890

Row Labels Min of TERM Count of GRADE First grade All grades

123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!

345678 3 1 A A #NUM!

456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11

* Need all grades where TERM = Min of Term

View 7 Replies
View Related
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

View 3 Replies
View Related
Jul 16, 2012

I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.

I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.

I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.

View 4 Replies
View Related
Nov 5, 2011

I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.

(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)

I'm setting up a table that grabs data from columns in a raw data spreadsheet.

For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0

So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.

So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.

This is the exact formula I am using:

=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))

Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.

Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.

View 3 Replies
View Related
Nov 28, 2012

Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.

I would like to be able to get a list of all values in a given column that match a specific name in another column.

Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.

For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.

The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.

CPT Sample Book.xlsx

View 4 Replies
View Related
Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 Replies
View Related
Sep 30, 2009

Is it possible to compare the number in one column with multiple other columns (same row) and return the lowest of all to a cell?

View 3 Replies
View Related
Oct 9, 2006

I found this spreadsheet on ozgrid, but can not locate the original post to move my question to -- mods please move if you can.

As for the question:

The spreadsheet attached searches an array, and shows the top 3 scoring names. How do I modify it to show 3 lowest scoring names without changing the rank field?

View 9 Replies
View Related
Nov 8, 2006

I am looking for a formula that will return the lowest value in a range, expressed as a letter.

The relative values of these letters are:

F=0

P=1

M=2

D=3

For example,

If in cells A1:A4 we entered the values F, P, M & D in A5 the result would be F (as it is the lowest value in the A1:A4 range).

I have included an example worksheet with fictional data and the intended result in the Final Score column.

View 7 Replies
View Related
Apr 22, 2014

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

View 2 Replies
View Related
Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A

B

C

1

Cat 1

January 1, 2014

John

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;

C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1

C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1

C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

View 2 Replies
View Related
Oct 15, 2007

I have this issue and I can´t find the solution .

I need to have sometihng that copies only the last ( cell in the right) non-zero cell and past in the same row.

This macro or VBA code needs to search the row, and paste in a column the value of the last cell in the right.

The sheet I´m working have lots of rows and lots of columns. The columns are dates. And I need to have just a column in the end with the most recent data of each row.

View 5 Replies
View Related
Aug 2, 2008

I need a formula to remove all alphabet in a cell,for instance:-

A1=130 sheets returns 130

A2=118pcs returns 118

A3=approx123 returns 123

View 4 Replies
View Related
Jan 21, 2014

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service

B = From

C = To

D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green

2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue

3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

View 3 Replies
View Related
Sep 19, 2005

I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:

K20) - these are my Numeric Labels.

I then have several other Rows that span the same number of columns as the

Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric

Labels.

I would like to AVERAGE the Numeric Values in each Row and have a Numeric

Label (value) that corresponds to the calculated average returned as the

result.

Example: Sample Data

A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90

A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10

Based on the above data - the Expected Result is 81

Average = 6; Return Numeric Label = 81

The leading zeros shown in A21:K21 is for alignment purposes only.

I would appreciate two formulas:

1) Includes zeros to be averaged

2) Excludes zeros from being averaged

I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),

$A21:$K21,0)) but receive the #N/A error message.

View 14 Replies
View Related
Apr 19, 2009

I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".

Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.

I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.

View 4 Replies
View Related
Dec 15, 2013

[URL]

What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.

View 9 Replies
View Related
Feb 4, 2014

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.

View 3 Replies
View Related
Jan 12, 2006

I would like a flexible Formula to Return the Column Number of individual

Numeric Labels and their Numeric Value. I have a 2-Row by many Columns

chart/ grid layout. My 1st Column, Column Number "1" is Excel Column Letter

"F".

Numeric Label 17 is housed in Column Number "12" per my chart , its Numeric

Value 5 is housed on the next Row - directly below the Numeric Label.

Sample Data: ...

Scenario:

To Return the correct Column Number: locate the Numeric Label 17 and its

Numeric Value on the Row below, Numeric Value is 5. The Numeric Value is to

be increased by a Value of 1 (one) - NEW Numeric Value = 6. The Column

Number Returned should reflect Numeric Label of 17 remains the same but the

Numeric Value is increased by one.

To Return a Column Number representing the Numeric Label 17 and its original

Numeric Value +1 (plus one): the Numeric Label is still 17 but the Numeric

Value is NOW 6. From the oringal Column Number that housed Numeric Label 17,

that is Column Number "12", SEARCH in Ascending order: moving LEFT from

Column Number "12" and using the Numeric Value as the 1st (first) search

order and the Numeric Label as the 2nd (second) Ascending search order.

SEARCH the Numeric Value Row until the first Numeric Value either equal to 6

or more than 6 is found. Then Return the Column Number of the Column to the

Right of that Numeric Value.

Required Result:

Return the Column Number based on the Numeric Label 17 and the NEW Numeric

Value of 6, search in Ascending order - First search order based on Numeric

Value 6 - Second search order based on Numeric Label 17.

Returned Result should be Column Number 10.

View 14 Replies
View Related
Jul 5, 2006

Using a Dynamic Named Range "Data", I would like a Formula to return the Row

Number of the "LAST" instance of a duplicate numeric value - repeating twice

consecutively in the same column.

View 10 Replies
View Related
Jan 23, 2012

Is there a formula to look in multiple arrays and return whatever it finds i.e. text or numeric values?

Sheet1 Â ABCDEFGH1Cat0Â CatFiveÂ Donkey32Â Â Â Mouse2Â Wolf43Â

4WolfFourÂ CatFiveÂ Donkey35Â Â Â Mouse2Â WolfFourSpreadsheet FormulasCellFormulaB1=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,$D$1:$E$2,2,0),

VLOOKUP(A1,$G$1:$H$2,2,0)))B4=LOOKUP(REPT("z",255),CHOOSE({1,2,3},0,

VLOOKUP(A4,$D$4:$E$5,2,0),VLOOKUP(A4,$G$4:$H$5,2,0)))

View 9 Replies
View Related
Apr 23, 2008

I need to create a formula (not a VBA code as it is a shared spreadsheet) to bring up the next number in a sequence. For example

1

2

4

5

The outcome would be 3 as it is the first number that has not been used.

View 2 Replies
View Related
Sep 22, 2009

Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.

I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference

View 5 Replies
View Related
Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _

"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

View 1 Replies
View Related
Mar 13, 2008

I want a cell to return the lowest value in a time series of data that comes AFTER the highest value in the range (so date specific). I have the formula for finding the highest value. The time series range changes on a rolling 1 year basis and I have attached the file. The cells highlighted in orange are the ones that need calculating.

View 2 Replies
View Related
Dec 12, 2013

I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?

Item

lot

Amt

Items

Lot #s

Yds

10429.50.0

N49950

2.5

0201.019FLAME.0

T37530

9

[code]....

View 5 Replies
View Related