Lookups with Min and Max values

hayfish78

New Member
Joined
May 26, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

Looking for some help, I have large set of numbers and Im trying to pull information from to create a help / crib sheet.

Column M is the reference number and im looking for a formula for Column J and L to provide the Min and Max Numbers associated Colum M based on the values in Column F and G,

I have manually inserted the min / max values for Column M value 0 to show what im looking for, so 0 is equal to a min value of 0 and a max of 0.5, 1 has a min value of 1.6 and max 2.4

Any help gratefully appreciated as im struggling to come up with something
 

Attachments

  • Untitled.png
    Untitled.png
    20.5 KB · Views: 6

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You can use the MINIFS & MAXIFS functions to do that.
 

hayfish78

New Member
Joined
May 26, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You can use the MINIFS & MAXIFS functions to do that.
Hello

Ive tried to create something using the min and maxifs statements but struggling to understand the criteria to be honest
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
Office Version
  1. 365
Platform
  1. Windows
In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,144,281
Messages
5,723,473
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top