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

Sam,

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

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529246

Slight typo.

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

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529246

=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),E4:AC 4,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

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

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:AC 5 ))

Any suggestions?

Cheers,
Sam

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


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

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:A C4,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,

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529246

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

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

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529246

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

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

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 ,
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!

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.

Domenic's formula catches that

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

again array entered

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Herbert Seidenberg" wrote in message
oups.com...
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 Domenic,

Thanks for additional input - much appreciated.

Cheers
Sam

Domenic wrote:
Try...
=INDEX(E5:AC5,MATCH(MIN(IF(E6:AC6=8,E4:AC4)),IF(E 6: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