Match 3 Criteria and Return Lowest Numeric Value

Sam via OfficeKB.com · 04-03-2006, 11:58 AM

Hi All,

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

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1

**SteveG** · 04-03-2006, 01:13 PM

Sam,

=SUMPRODUCT((E6:AC6=8)*(E4:AC=MIN(E4:AC4)),(E5:AC5))

HTH

Steve

**SteveG** · 04-03-2006, 01:14 PM

Slight typo.

=SUMPRODUCT((E6:AC6=8)*(E4:AC=MIN(E4:AC4))*(E5:AC5 ))

Steve

Bob Phillips · 04-03-2006, 01:40 PM

=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),E4:AC4,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:5e3b17f7d703b@uwe...
> Hi All,
>
> 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
>
> Thanks
> Sam
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200604/1

Herbert Seidenberg · 04-03-2006, 01:50 PM

E4:AC= ?
With the given data (E4:L4) try
0, 30 and 7

sam518 via OfficeKB.com · 04-03-2006, 01:55 PM

Hi Steve,

Thank you very much for assistance.

Using the Formula below, I get an unexpected Result of zero.
=SUMPRODUCT((E6:AC6=8)*(E4:AC4=MIN(E4:AC4))*(E5:AC5 ))

Any suggestions?

Cheers,
Sam

SteveG wrote:
>Slight typo.
>=SUMPRODUCT((E6:AC6=8)*(E4:AC=MIN(E4:AC4))*(E5:AC5 ))
>Steve
>

--
Message posted via http://www.officekb.com

**SteveG** · 04-03-2006, 02:06 PM

Sam,

In your range E6:AC6, is there an occurance of 8 where E4:AC4 is less than the MIN in your example and if so is there a 0 or no data in E5:AC5? That would return a zero.

Steve

Sam via OfficeKB.com · 04-03-2006, 02:10 PM

Hi Bob,

Thank you very much for your assistance. Your Formula worked a treat. Great!

Cheers
Sam

Bob Phillips wrote:
>=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),E4:AC4,0))
>which is an array formula, it should be committed with Ctrl-Shift-Enter, not
>just Enter.
>
>> Hi All,
>>
>[quoted text clipped - 32 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

Sam via OfficeKB.com · 04-03-2006, 02:30 PM

Hi Steve,

Thanks for reply.

The data in E4:AC4 is ok and there is data in E5:AC5 and no zero.

Cheers
Sam

SteveG wrote:
>Sam,
>In your range E6:AC6, is there an occurance of 8 where E4:AC4 is less
>than the MIN in your example and if so is there a 0 or no data in
>E5:AC5? That would return a zero.
>
>Steve
>

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1

Herbert Seidenberg · 04-03-2006, 02:50 PM

If your data looks like this and your input is 30,
then Steve's formula will give you 0 as an answer
and Bob's will give 23 as the wrong answer.
Val 145 127 120 160 130 170 160 160 170
Lab 20 21 22 23 24 25 26 27 28
Ref 8 0 8 2 0 10 8 30 30

Input
30
Output
27

**SteveG** · 04-03-2006, 02:51 PM

Sam,

My post worked in your example but unfortunately, it only works because the MIN of the range E4:AC4 was assigned the number 8. Sorry for the bad info. I'll try again.

Steve

Sam via OfficeKB.com · 04-03-2006, 03:35 PM

Hi Herbert,

I've used the same data below and I'm getting error #VALUE when using the
Input as 30 or 8?

=SUMPRODUCT((E6:M6=30)*(E4:M4=MIN(E4:M4))*(E5:M5 ))

Cheers,
Sam

Herbert Seidenberg wrote:
>If your data looks like this and your input is 30,
>then Steve's formula will give you 0 as an answer
>and Bob's will give 23 as the wrong answer.

>Val 145 127 120 160 130 170 160 160 170
>Lab 20 21 22 23 24 25 26 27 28
>Ref 8 0 8 2 0 10 8 30 30

>Input
>30
>Output
>27

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1

Domenic · 04-03-2006, 03:45 PM

Try...

=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),IF(E6:AC6=8,E4:AC4),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <5e3b17f7d703b@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi All,
>
> 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
>
> Thanks
> Sam

Domenic · 04-03-2006, 03:55 PM

Also, if there's more than one 'Numeric Value' with the lowest value,
the formula will return the first occurrence. If you want to return all
corresponding 'Numeric Labels', the formula would need to be modified.

Hope this helps!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Try...
>
> =INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),IF(E6:AC6=8,E4:AC4),0))
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!

Herbert Seidenberg · 04-03-2006, 04:00 PM

If you copied the data from this site,
you will get unusual spaces (Alt 0160) in your data.
Try typing in the data manually.
If Steve or Bob have not replied in 2 hours,
I will post my formula.

Bob Phillips · 04-03-2006, 05:20 PM

Domenic's formula catches that

=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=F1,E4:AC4)),IF(E6:AC6=F1,E4:AC4),0))

again array entered

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Herbert Seidenberg" <[email protected]> wrote in message
news:[email protected]...
> If you copied the data from this site,
> you will get unusual spaces (Alt 0160) in your data.
> Try typing in the data manually.
> If Steve or Bob have not replied in 2 hours,
> I will post my formula.
>

Sam via OfficeKB.com · 04-03-2006, 07:25 PM

Hi Domenic,

Thanks for additional input - much appreciated.

Cheers
Sam

Domenic wrote:
>Try...
>=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),IF(E6:AC6=8,E4:AC4),0))
>...confirmed with CONTROL+SHIFT+ENTER.

>Hope this helps!
>
>> Hi All,
>>
>[quoted text clipped - 32 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

**un1ty** · 11-27-2012, 05:34 AM

Originally Posted by Bob Phillips

Domenic's formula catches that

=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=F1,E4:AC4)),IF(E6:AC6=F1,E4:AC4),0))

again array entered

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Herbert Seidenberg" <[email protected]> wrote in message
news:[email protected]...
> If you copied the data from this site,
> you will get unusual spaces (Alt 0160) in your data.
> Try typing in the data manually.
> If Steve or Bob have not replied in 2 hours,
> I will post my formula.
>

Hi Bob, i have a similar issue and cannot find a solution.
i have made a schedule for me and my co-workers.
there is a boatload more of information in the excel sheet so i cannot send it whole, but i will describe my issue as good as i possibly can.
i have a couple of arrays.
Sheet 1, displays all current information
B2:B35 contains a list of names of all my coworkers, labelend "COWORKERS"
E2:E35 -should- Display the first date/time in another array, on a different page that has been labeled ' next shift '

in sheet 2, contains the complete schedule information, this page automatically lines up all information from the schedule in a database
A2:A101 is an array which contains the name of the worker that have been scheduled labeled "COWORKERS SCHEDULED"
B2:B101 is an array which contains the time their shift starts. labeled "NEXT SHIFT" .

So what do i want it to do?
For exampe:
Sheet 1, B2 contains the name "Robin"
Sheet 2, A8, A18, A21 contain "Robin" (but this can occur anywhere else in the same column)
Sheet 2, B8, B18, B21 contain (resp.) 11-12-2012, 08-9-2012, 13-01-2013

so for this example i want it to -find- all rows in Sheet 2 Column A, that contain the same "Text" as is displayed in Sheet 1 B2,
return -ALL- dates in the corresponding row in the 2nd (B) column, and only display the lowest. in this example Sheet 1 Cell E2 should display " 08-09-2012 "

i have made some progress in 'a' formula, but the problem was that it would only return the first hit it found, e.g. 11-12-2012 instead of the lower value.

could you please help me out?

Kind regards,

Robin

**Fotis1991** · 11-27-2012, 06:06 AM

Hi Robin.

We'll be glad to help you, but according forum rules you have to start your own thread.

And my suggestion is to upload a sample workbook.

**un1ty** · 11-27-2012, 06:38 AM

nevermind, by accident i just entered the right formula!
im now going to expand it to my liking

Match 3 Criteria and Return Lowest Numeric Value

LinkBack

Thread Tools

Rate This Thread

Display

Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Re: Match 3 Criteria and Return Lowest Numeric Value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions