Find narrowest value with formula for two or more criterias

[Benedikt]

Dear all,

Currently I am setting up a excel list in order to evaluate how much items I get, for a certain size of my article. Each is defined by two dimensional sizes. Inside diameter and Cross-section. The result should be a certain number in column A

I have tried to set the formula with INDEX and MATCH comnining two criteria (ID&CS) to look for in the column B&C. For exact matches it really works well, but my major question is right now I want to look for a dimension which is in between the sizes and not an exact match.

Here I would like to have the quantity from column A for the smallest dimension which is above the criteria given.

Example:

1 x 1,25 mm is in the list
2 x 1,25mm is given in the list

Now I search for 1,5 x 1,25mm and it should give me the quantity from 2 x 1,25mm

Same thing should be

Now I search for 2,2 x 1,20 it should give me the quantity for 3 x 1,25mm.

So far I couldn't figure out the right formula for this search. Also I have tried VLookup with two criteria but this doesn't work either.

Thanks a lot for your input and support

Greets
Benedikt

 

[Chihiro]

You'll need to give bit more sample. And sample outputs as well.

What should be the answer when ID min = 20.5 & CS = 1?

You only have data for when CS = 1. Can you provide more sample which will satisfy your criteria in initial post?

With current data. Something like below would work.

=LOOKUP(2,1/(B3:B70<=F1)/(C$3:C$70<=G1),A3:A70)

Where F1 holds ID min value, & G1 holds CS value.

 

[NARAYANK991]

Hi ,

Will the looked for value be in between two Inside Diameter values alone , or will it be between two Cross Section values alone or will it be a combination of both ?

For example , your data shows the same value for Cross Section throughout ; is this how your actual data will be ?

If not , please upload a workbook which has your actual work data.

As it stands , try this array formula , to be entered using CTRL SHIFT ENTER :

=IF($E$1 >= $B$70, $A$70, MAX(IF($B$3:$B$70 >= $E$1, $A$3:$A$70)))

Cell E1 contains a lookup value for the ID.

Narayan

 

[Benedikt]

Dear Narayan,

thanks for your feedback, I saw that I have excluded some data accidentally. Please see attached the new workbook with also some examples and what should be looked for. In the past I have also tried to combine INDEX + MATCH but it only gives me back an error, when entering 1 or -1 for narrowest values.

Hopes that helps to understand the complex relations between my values.

 

[NARAYANK991]

Hi ,

In the last example you have given , the values being looked for are 3.55 and 1.11

The closest value to 3.55 is understandably 4 ; shouldn't the closest value to 1.11 be 1 ? Why has 1.25 been used ?

Narayan

 

[bosco_yip]

Hi,

Perhaps, see attached file.

Regards
Bosco

 

[Peter Bartholomew]

I have a problem with this question; more a question of understanding the logic than the Excel formulas. What I have understood from the question is that the closest match is sought based upon two criteria. If that is the case, the first step would be to merge the two criteria to produce a single criterion for ranking the closeness of a match.

In the attached, have used a quantity 'delta' based upon the absolute value of the deviation of each quantity. The minimum can be determined by exhaustive search and an index returned to identify the corresponding item.

 

[Benedikt]

Hey there,
thanks so much for your input. Surely the best would be to have i.e. CS 1,11 going back to the CS 1,0 and 1,78 going forward to 2,0mm. But I assumend this being too difficult.
Your solution i.e. 1,11mm going back to 1,0mm is even more precise.

@Peter: Sorry I didn't get the question.

 

[Benedikt]

the goal is to being able to enter all possible values in F6 and F7 and that F13 gives back the search result for the respective next bigger dimensions, which are subject to column B and C.

 

[Peter Bartholomew]

@Benedikt
I interpreted the 'narrowest value' as including values both above and below the nominal value. That is easy enough to correct with an IF statement.

My point was that, with two measures, there may be two distinct options that satisfy the condition of being 'next biggest' in situations in which every combination of the two measures is not available. For example if you specified (1.5, 1.1) and the choice were (2, 1.5), (3, 1.25), (3, 1.75), and (4, 1.5) which would you choose? In your case, I see that (2, 1.25) is available as a choice so there is no problem.

 

[Benedikt]

Thanks all,
the formulas worked very well. Great work from you.

I have also found the solution with Index + Match + Ceiling. That always gave me the next bigger dimension on both values and finally Excel could find a match.

Now I am setting it up as VBA. But couldn't find a way so far to include Ceiling funtion into VBA. Maybe somebody has a hint here.

Dim moq As Double
Dim ID As Double
Dim CS As Double
Dim x As Double
Dim y As Double
moq = Sheets("NBR Richie1").Range("B:B"))
ID = Sheets("NBR Richie2").Range("C:C"))
CS = Sheets("NBR Richie3").Range("D:D"))
x = Me.txtID
y = Me.txtCS

If cboMaterial.Value = "NBR" Then
txtMOQ.Value = Application.WorksheetFunction.Index(moq, Match(x & y, ("ID") & ("CS"), 0))

In Excel I had Ceiling before x and y.

cboMaterial is a comboBox where you can select different terms, such as "NBR". txtID is the ID (as in excel file) and txtCS is the CS (as in Excel)

Please let me know if you need more information.