Excel question

**vegyjones** · 20th February 2007

Right, getting off the plumbing questions in the PC section and back to computer advice

If I have a list in excel as such

Files 3456-3458
Files 3459-3478
Files 3479-3484

Can I do a search for File 3465 to get it to bring up the 2nd item in the list (3459-3478)???

**mathare** · 20th February 2007

Originally Posted by vegyjones

If I have a list in excel as such

Files 3456-3458
Files 3459-3478
Files 3479-3484

Can I do a search for File 3465 to get it to bring up the 2nd item in the list (3459-3478)???

Why would that search bring up the 2nd item in the list? 3465 doesn't appear anywhere in the data

**vegyjones** · 20th February 2007

No, but I have some stuff to jot down.
It will say something like 3459 to 3478

Is there no way I could put that down
so that it would know a file in between those numbers is there!

**mathare** · 20th February 2007

Originally Posted by vegyjones

No, but I have some stuff to jot down.
It will say something like 3459 to 3478

Is there no way I could put that down
so that it would know a file in between those numbers is there!

Oh! I see! They are numerical ranges not hyphenated numbers, right.

How are you planning to use this search facility? A normal find works on a string match which wouldn't work in this case as a search for 3465 wouldn't find a match anywhere in your data.

You need the row (basically) containing a file number lower than (or equal to) the search string and one greater than (or equal to) it don't you? What will you then do with that row number?

**vegyjones** · 20th February 2007

Every few set of files will be in an individual box, so the search wil only be to determine which box the particular file is in!

So if I do the start and finish file in different columns,
and then do search for file number it will take me to the closest figure, yes?

**mathare** · 20th February 2007

Originally Posted by vegyjones

Every few set of files will be in an individual box, so the search wil only be to determine which box the particular file is in!

So if I do the start and finish file in different columns,
and then do search for file number it will take me to the closest figure, yes?

Enter the start file number in one column, the end file number in another and the box number in a third (hidden) column. Then use the LOOKUP function to return the box number, as in the attached.