Comments on Yogesh Gupta's Excel Tips: Excel Formula : Closest Match

Hello Rickard What you are aksing for is possible...

2010-02-05T14:30:23.139+05:30

Hello Rickard

What you are aksing for is possible technically and will need lots of formulas. Instead I will go for a PivotTable and filter it for TOP 10 values. This will be more robust and easy way to do what you need.

Let me know in case you need any help in making PivotTables and filtering them on TOP 10 Values.

Can I some how use the same formula to get the top...

2010-02-05T13:07:49.632+05:30

Can I some how use the same formula to get the top 10 orders with customer name, value etc. Ie the hole row?

Hi Yogesh..you are right my formula does not handl...

2010-01-20T13:34:09.726+05:30

Hi Yogesh..you are right my formula does not handle multiple columns...
May be we can modify your formulas a bit
=SMALL(Data,COUNTIF(Data,"<="&Target)))
will work. So we dont need the IF if an exact match is available

sam

Hello Sam Thanks for sharing file with the formul...

2010-01-19T21:35:15.222+05:30

Hello Sam

Thanks for sharing file with the formula. Now I realised that why this was not working when I tried to use this formula.

This array formula fails to work in case the Data range in more than one column. I was trying it with data in two column and was getting error.

Thanks
Yogesh Gupta

Yest it does...I have uploaded the file. I have be...

2010-01-19T10:17:45.955+05:30

Yest it does...I have uploaded the file. I have been using it for many years

http://cid-af0f671fc64e8122.skydrive.live.com/self.aspx/New%20folder/CMATCH.xls

sam

Hello Vipul Thanks for your inputs, however we ar...

2010-01-18T21:30:56.980+05:30

Hello Vipul

Thanks for your inputs, however we are dealing with unsroted range here. Some times it is not possible to sort a range just to make a particular formula work.

Regards//Yogesh Gupta

Hello Sam Thanks for sharing this formula, howeve...

2010-01-18T21:29:10.671+05:30

Hello Sam

Thanks for sharing this formula, however this does not work on the data I have in file uploaded along with this post. I entered it as CSE (Array) formula but results into error.

You wanna give it a try ? share your file if you succeed
Regards//Yogesh Gupta

Limitation being - the data has to be sorted by th...

2010-01-18T18:09:16.538+05:30

Limitation being - the data has to be sorted by the lookup value.

Same can be achieved using the lookup formulae and...

2010-01-18T18:08:29.430+05:30

Same can be achieved using the lookup formulae and also a comination of index() and match() formulae by using the appropriate value in the last argument of lookup/match formulae.

=INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-T...

2010-01-18T16:30:41.420+05:30

=INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) array entered. - for the closest match

sam