Find Specific Number - If not found find next lowest number

Hola! So I'm a bit stuck at the moment. I'm trying to search a column for a specific number which is set by an input box. The column is sorted from Largest > Smallest. I originally had this code to find the number:

Please Login or Register  to view this content.

So what I'm trying to do is find the very first cell that contains the specified number. The problem is if the number isn't found the script fails. I would like it to search for the next closest number that is smaller than the originally number. I've tried using a For/Next statement but that has to search line by line and takes a bit more time. Is there a way to do what I need that is fast? Any help is greatly appreciated!

If your search selection is a whole column

Please Login or Register  to view this content.

If it is not, then the row number will need to take account of first row of selection

Please Login or Register  to view this content.

Hi,

I generally prefer to use Excel standard functionality to find stuff like this and then if required read the result into VBA rather than jump through hoops getting VBA to do something which is far more efficiently done by Excel.

In this case the array formula (2nd below) below - entered with Ctrl-Shift-Enter will give you the relevant row number in a range.

First though I prefer to work with dynamic range names so that I don't need to worry about the size of ranges. In my example I have a list of numbers in A1:A20 and I have defined the name 'lst' as

Formula:

Please Login or Register  to view this content.

Now the array formula that returns the row number of the value you seek, where C1 is the value required

Formula:

Please Login or Register  to view this content.

@Richard Buttrey

Formula:

Please Login or Register  to view this content.

elegant solution
but
Can you explain the principle of how the formula works
thanks

Originally Posted by kev_

@Richard Buttrey

Formula:

Please Login or Register  to view this content.

elegant solution
but
Can you explain the principle of how the formula works
thanks

Hi,

I'll take it as read that you understand the dynamic range name 'lst'

The

Formula:

Please Login or Register  to view this content.

returns a list of numbers from 1 to the number of rows in the range called 'lst', i.e. the numbers 1,2,3.....20. These values will be used for the index number for the SMALL() function.


When the MATCH(C3,SMALL(lst bit is added to the above and since the whole thing is an array formula the MATCH(C3 looks for the C3 value in the lst array. Since it's a match function it returns the position of C3 in the list, i.e. the row number in the lst range.

Since the MATCH is primarily used on an ascending list and finds the nearest value in the ascending order, and since the array returned from the rest of the formula is in ascending order the MATCH returns the position of C3 in that array. However since the actual list on the sheet is in descending order we need to deduct the value the MATCH returns from the total number of values in the list. This is the COUNT(lst)-MATCH....bit

Hence it returns the row number required. When the value is not in the list MATCH finds the next highest value and drops back one 'row'.

The final bit +ROW(lst) always returns the value of the first row in the list and is required to adjust for those occasions when the lst does not start at row 1. i.e. it adds the number of rows between Row 1 and the start of the list.

Hi Richard
Thank you