VBA Find Closest value w/o going over

**alrichar** · 09-15-2015, 10:07 AM

Hello. I am looking for a way to determine the closest value in list B compared to values in list A. Neither List A or List B have unique identifiers. The values in column C needs to be the closest match to list B without going over. After endless attempts to solve this issue with excel functions, it seems like vba is going to be the best option.

I have this macro that I am trying to tweak but I am not having any luck. Can somebody help me with this code? It needs to match based on columns A & columns H. I also need this code to loop through all of the values in list A.

Please Login or Register  to view this content.

**pjwhitfield** · 09-15-2015, 10:12 AM

Can you just confirm the test data youve shown please?

You say "The values in column C needs to be the closest match to list B without going over." however your test data shows Value of 19, Closest Value = 19.2, Value = 26.2, Closest Value = 55?

**alrichar** · 09-15-2015, 10:17 AM

Thanks for the correction. Sorry, I should have done a better job with the test data. It was manully populated.

List A
AA Value = 19, Closest AA Value determined from List B by VBA Code = 18
AA Value = 26.2, Closest AA Value determined from List B by VBA Code = 22
AAL Value = 33.3, Closest AAL Value determined from List B by VBA Code = 32
AAL Value = 38.3, Closest AAL Value determined from List B by VBA Code = 35

List B
AA = 18
AA = 15
AA = 22
AA = 55
AA = 19.2
AAL = 18.5
AAL = 32
AAL = 35
AAL = 28
AAL = 44
AAL = 41

Originally Posted by pjwhitfield

Can you just confirm the test data youve shown please?

You say "The values in column C needs to be the closest match to list B without going over." however your test data shows Value of 19, Closest Value = 19.2, Value = 26.2, Closest Value = 55?

**pjwhitfield** · 09-15-2015, 10:17 AM

Ive finally written my first Array Function! Enter this using Ctrl+Shift+Enter

Please Login or Register  to view this content.

**pjwhitfield** · 09-15-2015, 10:19 AM

However, this returns values less than the original (which matches the requirements) but not your amended examples?

Are you actually looking for items greater than the original value?

**alrichar** · 09-15-2015, 12:47 PM

Originally Posted by pjwhitfield

However, this returns values less than the original (which matches the requirements) but not your amended examples?

Are you actually looking for items greater than the original value?

Please Login or Register  to view this content.

Works perfectly. This is a lot easier than using a macro. Thanks for the help!

VBA Find Closest value w/o going over

LinkBack

Thread Tools

Rate This Thread

Display

VBA Find Closest value w/o going over

Re: VBA Find Closest value w/o going over

Re: VBA Find Closest value w/o going over

Re: VBA Find Closest value w/o going over

Re: VBA Find Closest value w/o going over

Re: VBA Find Closest value w/o going over

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to find a value in a column that is closest to a given value

[SOLVED] VBA Function to find closest value in row

[SOLVED] Find closest values to a given value?

[SOLVED] How to find the a value closest to a given value

Find the closest number

Using Find method to find the closest value.

Find closest value

Bookmarks

Bookmarks

Posting Permissions