Match 3 Criteria and Return Lowest Numeric Value

[Sam via OfficeKB.com]

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

 

[SteveG]

Sam,

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

HTH

Steve

 

[SteveG]

Slight typo.

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

Steve

 

[Bob Phillips]

=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)

 

[Herbert Seidenberg]

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

 

[sam518 via OfficeKB.com]

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

 

[Sam via OfficeKB.com]

Hi Bob,

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

Cheers
Sam

=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

 

[SteveG]

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]

Hi Steve,

Thanks for reply.

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


Cheers
Sam

 

[Herbert Seidenberg]

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]

Sam,

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

Stev

 

[Sam via OfficeKB.com]

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

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.

 

[Domenic]

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!

 

[Domenic]

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!

 

[Herbert Seidenberg]

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]

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)

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for additional input - much appreciated.

Cheers
Sam

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