Find the closest value in columns in excel

Hello,

I want to compare a value in one column with values in other three columns, then output the closest value (without going over) and the column name of the closest value.

For example, I want to compare the value in Y4 col to Y1,Y2 and Y3 and output the results in Closest Value and Closest Column.

Y1               Y2              Y3             Y4         Closest Value      Closest Column
20%      30%      21%      19%      20%               Y1
19%      20%      18%      17%      18%               Y3

I need an excel formula not the VBA code. I'd greatly appreciate any help!

Thanks
pmandairAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Try this to get the closest value instead:

=MIN(ABS((B2:D2)-F2))+F2

This is an array formula so need to confirm with "Ctrl  Shift + Enter" rather than just Enter.

This will take the closest, ie the value with the smallest difference between the comparison and the yearly values. When there are two values which are equally close to the comparison, ie one higher and one lower, it will take the one which is higher. If that is not correct then we will need to take another look.

Then for the column name use:

=INDEX($B$1:$D$1,MATCH($G2,$B2:$D2,0))

This will return the column name for the first occurrence of the value identified in the comparison. So if you have comparison value of 17% and Y1 and Y3 with 18% and 18% is found to be the closest, the INDEX will return Y1 as it is the first occurrence.
0
 
AlanConsultantCommented:
Hi,

See attached.

Does that do what you want?

Alan.
EE-29067381-Version1.xlsx
3
 
Rob HensonFinance AnalystCommented:
Normally I would suggest using MATCH function as Alan has in his solution.

However, MATCH has 3 options for the match type that it will look for.

Less than - will find the largest value that is smaller than or equal to the lookup value. The lookup range has to be in ascending order.
Exact match - as the name implies it will find an exact match. The order of the range does not matter.
Greater than - will find the smallest value that is greater than or equal to the lookup value. The lookup range has to be in descending order.

I am assuming the Y1 to Y3 reference is years 1 to 3 rather than cell references so the values are not going to necessarily be in ascending or descending order and there may not be an exact match, hence the requirement for closest, therefore none of the MATCH options will meet the requirement.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
AlanConsultantCommented:
Hi Rob,

Are you saying that what I posted does not give the correct answer for some cases?

If so, please can you give an example.

Thanks in advance,

Alan.
0
 
Rob HensonFinance AnalystCommented:
Sorry Alan, just taken a closer look at what you have provided and it does do what is required. I had only looked at the formula in column AE with the MATCH function to get the column address.

However, I think the OP is after the Y1 to Y3 reference rather than the physical column address.
0
 
AlanConsultantCommented:
No problem - you had me worried for a moment :-)

Thanks,

Alan.
0
 
Rob HensonFinance AnalystCommented:
@pmandair - can you clarify the layout of your data and the requirement. I suspect the alignment of columns has been messed up in the text lines in the question.

Your question suggests that you have 3 values with which to compare a 4th value.

Your first line then shows 3 values for Y1 to Y3 and a fourth value for Y4 and your 5th value which I assume has to be the result. Your 4th value of 19% is lower than all of the Y1 to Y3 values so there is no value that is less than or equal to the comparison value yet you have suggested the result should be 20%, ie the value from Y1; this is the closest but is higher than the Y4 value.
0
 
AlanConsultantCommented:
Hi Rob,

If so, then the attached is essentially the same, but returns the 'Year Number' rather than the cell address.

Alan.
EE-29067381-Version2.xlsx
0
 
Rob HensonFinance AnalystCommented:
Yes but, depending on clarification from OP, you have 4 values and a fifth for comparison. The question states 3 values and a fourth for comparison (Target). With both samples both of the Target values are lower than the Y1 to Y3 values so there is no value to return as they are all higher than the target.

See attached for how I think the data is supposed to be.
Y1_Y3-comparison.xlsx
0
 
AlanConnect With a Mentor ConsultantCommented:
Hi Rob,

I see what you are saying.

In that case, perhaps this makes more sense.

Alan.
EE-29067381-Version3.xlsx
0
 
Rob HensonFinance AnalystCommented:
But OP does not want to go over the target value:

then output the closest value (without going over)
0
 
AlanConsultantCommented:
I am thinking it might mean the opposite.

It's one or the other of the versions!

Think I'll leave it to the Author now :-)

Alan.
0
 
Rob HensonFinance AnalystCommented:
Indeed, need some clarification.
0
 
pmandairAuthor Commented:
Thanks Alan and Rob. The version3 is the correct depiction of what I was asking. This works like a charm.

Thanks again!
0
 
Rob HensonFinance AnalystCommented:
So, the opposite to what was in the question then??

Closest value which can be higher than the target value??

What if you have a scenario with target value of lets say 19% and you have Y1 and Y2 values of 18% and 20%. They are both equally close, which way would you go? Likewise, if you had target of 20% and values of 21% and 18%. 21% is closer but 18% is lower. Which way??

Thanks
Rob H
0
 
pmandairAuthor Commented:
Rob,

You're correct. My original post is a little confusing. I would say the closest, and you're right it could be either way - should be the closest in the range for the row.

Thanks
0
 
Rob HensonFinance AnalystCommented:
Not sure it is quite so simple as it has been so far if the closest can be either way.

Which way should it go if there are two options that are equally close, one higher and one lower by the same?
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Maybe this as a non-array formula which will return the lower of two that are equally close:

=IFERROR(INDEX($B3:$D3,MATCH(E3,B3:D3,0)),IF(RANK(E3,B3:E3,1)<3,MIN(B3:D3),IF(RANK(E3,B3:E3,1)=3,LARGE(B3:D3,2),MAX(B3:D3))))

Open in new window

This first checks to see if there is an exact match but if not it ranks the Y4 value in the range of values for Y1 to Y4.

Rank 1 - Y4 lower than Y1 to Y3 - result lowest of Y1 to Y3 as this will be next lowest value
Rank 2 - Y4 is within the range of values but only one value lower than it - result lowest of Y1 to Y3 as this will be next lowest value
Rank 3 - Y4 is within the range of values but only one value higher than it - result second highest of Y1 to Y3 as this will be next lowest value
Rank 4 - Y4 higher than Y1 to Y3 - result highest of Y1 to Y3 as this will be next highest value.
0
 
AlanConsultantCommented:
Having fun Rob?

;-)
0
 
AlanConsultantCommented:
The question has been flagged to be closed stating, 'No comment from Author to confirm solution.'

This is not true - the author explicitly stated, of the solution I posted that, 'The version3 is the correct depiction of what I was asking. This works like a charm.'

I propose that this question should be closed with my comment as the answer.

Alan.
0
 
Rob HensonFinance AnalystCommented:
Until the author then realised that his question was confusing and there was no decision as to what result he really needed; hence the 50/50 split of points.

Apologies, it looks like I chose the Author's comment rather than yours to split the points.
0
 
AlanConsultantCommented:
No problem if it was an error - just change it.

Thanks,

Alan.
0
 
Rob HensonFinance AnalystCommented:
No confirmation from author for preferred solution.
0
 
pmandairAuthor Commented:
Hi Rob,

I am so sorry to open this topic again. I used the Min and Index functions - it works fine if the values you're comparing to are higher than the ones that's getting compared. However, it doesn't work both ways.

I am attaching a sample file and you'll have a better idea what I'm asking. Comparing Y4 to Y1, Y2 and Y3 and the results are output in cols F and G. I have indicated the desired results where the formula didn't work. Any help to work it both ways would be appreciate.

Thanks!
Sample-File-Percent-Comparison.xlsx
0
 
Rob HensonFinance AnalystCommented:
Using the non-array formula in comment 42365676 works on all but one of those listed.
0
 
AlanConsultantCommented:
Hi pmandair,

You should really have responded in a timely manner in the first place so that Rob did not close this question on you, rather than ignoring us, then coming back after he closed it.

However, the attached formulae appears to match all of your amended columns.


Alan.
0
 
pmandairAuthor Commented:
Thanks Alan.

Are you referring the attached formulae to the one in comment 42365676?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.