Match 3 Criteria and Return Lowest Numeric Value

  • Thread starter Thread starter Sam via OfficeKB.com
  • Start date Start date
S

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
 
=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)
 
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
 
Hi Bob,

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

Cheers
Sam

Bob said:
=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,
Click to expand...
[quoted text clipped - 32 lines]
Thanks
Sam
Click to expand...
Click to expand...
 
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
 
Hi Steve,

Thanks for reply.

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


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.
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 becaus
the MIN of the range E4:AC4 was assigned the number 8. Sorry for th
bad info. I'll try again.

Stev
 
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 said:
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.
Click to expand...
 
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!
 
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!
 
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(E6:AC6=F1,E4:AC4),0))

again array entered

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top