How to return closest value based on two criteria?

[bhorsley89]

I'm wanting to return a value that corresponds to another value that is closest to a target value that I determine. For example, in cell H4, I input the number 6 which returns 113 in cell J4. This currently works as 6 is closest to 5 in column B (mins) and the value 113 in column C (var1) is associated with this row index.

The formula I'm using to achieve this is:
=INDEX(C4:C9,MATCH(MIN(ABS(B4:B9-H4)),ABS(B4:B9-H4),0))

However! I'd like to apply the same thought process and formula to find the closest value to a target (cell H4) but based on the value in cell I4 (test type). So, in essence, how can I edit my formula to return a value in column C (var1) that is based on finding the closest value in mins (column B) to that in cell H4, but only for tests (column A) that match cell I4?

Note, I'd like to achieve these two things within the one formula and cell (J4). In addition, like the current formula, if a number I input in cell H4 doesn't exist, I'd still like the next closest to be returned.

 

[Peter_SSs]

Welcome to the MrExcel board!

What should happen if there was two equal closest values? For example in H4 if you had 4 with Test 1 in I4.
3 and 5 are both equally close to 4, so what to do?
Would it be 117 or 113 or something else?

 

[bhorsley89]

Welcome to the MrExcel board!

What should happen if there was two equal closest values? For example in H4 if you had 4 with Test 1 in I4.
3 and 5 are both equally close to 4, so what to do?
Would it be 117 or 113 or something else?

That's a good question. In this situation, I think my preference would be to return 117 (3 mins). So, if a number falls within two directly either side of it, return the smallest (where possible). Having said that, in the scenario where I have 1 set as the target value in H4 and it does not exist based on I4, I'd like the next number returned.

Know I'm asking a lot here, but this is a unique scenario for a project I'm working on.

 

[Peter_SSs]

Having said that, in the scenario where I have 1 set as the target value in H4 and it does not exist based on I4, I'd like the next number returned.

Not sure that I followed that, but see if this does what you want. If not, please provide more varied sample data, expected results and further clarification.

If providing sample data I suggest that you investigate XL2BB to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

24 10 24.xlsm
	A	B	C	D	E	F	G	H	I	J
3	test	mins	var1					mins	Test 1	Output
4	Test 1	3	117					6	Test 1	113
5	Test 1	5	113					4	Test 1	117
6	Test 1	8	114					6	Test 2	92
7	Test 2	2	103					4	Test 2	92
8	Test 2	4	92					1	Test 2	103
9	Test 3	7	100					2000	Test 3	100
Closest

Cell Formulas
Range	Formula
J4:J9	J4	=LET(f,SORT(FILTER(B$4:C$9,A$4:A$9=I4),1),INDEX(SORTBY(f,ABS(H4-TAKE(f,,1))),1,2))

 

[bhorsley89]

Excellent! This does the job. Thanks!

Not sure I fully understand the LET() function, but it returns what I want. Appreciate the fast response.

 

[Sanjeev1976]

You can try the below :

Book2
	A	B	C	D	E	F	G	H	I	J
1
2	Example Data
3	test	mns	var1					mins	Test	Output
4	Test 1	3	117				Enter desired mins	6	Test 1	113
5	Test 1	5	113
6	Test 1	8	114
7	Test 2	2	103
8	Test 2	4	92
9	Test 3	7	100
Sheet1

Cell Formulas
Range	Formula
J4	J4	=VLOOKUP(H4,B4:C9,2)

 

[Peter_SSs]

Excellent! This does the job. Thanks!

You're welcome. Thanks for the follow-up.

Not sure I fully understand the LET() function,

LET allows us to introduce a name/variable (f in my case) and give it a value to be used (possibly a number of times) later in the formula.
=LET(f,SORT(FILTER(B$4:C$9,A$4:A$9=I4),1),INDEX(SORTBY(f,ABS(H4-TAKE(f,,1))),1,2))
In my example, f is given the values returned by the blue text. Those values are then used twice where the red f's appear.
LET can make the formula shorter and more efficient because it does not have to calculate the same information twice.
If we did not use LET my formula would be written
=INDEX(SORTBY(SORT(FILTER(B$4:C$9,A$4:A$9=I4),1),ABS(H4-TAKE(SORT(FILTER(B$4:C$9,A$4:A$9=I4),1),,1))),1,2)
which is both longer and the purple part needs to have exactly the same thing calculated twice.

You can try the below :

I'm thinking that you may have missed this request..

find the closest value to a target (cell H4) but based on the value in cell I4 (test type). So, in essence, how can I edit my formula to return a value in column C (var1) that is based on finding the closest value in mins (column B) to that in cell H4, but only for tests (column A) that match cell I4?