The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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.

However, I think the OP is after the Y1 to Y3 reference rather than the physical column address.

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.

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

Alan.

EE-29067381-Version2.xlsx

See attached for how I think the data is supposed to be.

Y1_Y3-comparison.xlsx

I see what you are saying.

In that case, perhaps this makes more sense.

Alan.

EE-29067381-Version3.xlsx

But OP does not want to go over the target value:

then output the closest value (without going over)

It's one or the other of the versions!

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

Alan.

Thanks again!

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

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

Which way should it go if there are two options that are equally close, one higher and one lower by the same?

=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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial```
=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))))
```

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.

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.

Apologies, it looks like I chose the Author's comment rather than yours to split the points.

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

Using the non-array formula in comment 42365676 works on all but one of those listed.

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.

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

See attached.

Does that do what you want?

Alan.

EE-29067381-Version1.xlsx