Is there such a formula?

  • Thread starter Thread starter Renee
  • Start date Start date
R

Renee

I have an Excel spreadsheet with 2 columns. The first
column has a Box number (example: 01245). The second
column has the file numbers that are in that particular
box (example: 821-873). So, in other words, box #01245
contains files 821, 822, 823, 824, etc. all the way to
873.

Obviously it's difficult to find a particular file number
if we don't know what file number range it's in. Rather
than list each file number separately (Example: first
column I would list the box number and then in the 2nd
column list the first file #, then I would go down to the
next row and list the box # again with the next file #
and so forth) in order to search for a specific file #.

Is there a formula of some kind whereby I can continue to
include the file # ranges (example: 821-873) but be able
to do a search to find one specific file number
(example: I want to find file # 846)? Is this possible
and if so, how do I do it?

Thanks for any help anyone can give me!
 
I have an Excel spreadsheet with 2 columns. The first
column has a Box number (example: 01245). The second
column has the file numbers that are in that particular
box (example: 821-873). So, in other words, box #01245
contains files 821, 822, 823, 824, etc. all the way to
873.

Obviously it's difficult to find a particular file number
if we don't know what file number range it's in. Rather
than list each file number separately (Example: first
column I would list the box number and then in the 2nd
column list the first file #, then I would go down to the
next row and list the box # again with the next file #
and so forth) in order to search for a specific file #.

Is there a formula of some kind whereby I can continue to
include the file # ranges (example: 821-873) but be able
to do a search to find one specific file number
(example: I want to find file # 846)? Is this possible
and if so, how do I do it?

Thanks for any help anyone can give me!
Click to expand...

Although with string manipulation, you could set up things they way you have
posted, I will suggest that the formula would be simpler if you set up three
columns.

Box StartFile EndFile
#01234 821 873
#01235 874 906
....

Define names as the column headers. So that Box, for example is A2:A100, etc.

Assuming that the file numbers are contiguous, and you the file you are looking
for is in F7, then:

=IF(F7>MAX(EndFile),"not in boxes",INDEX(Box,MATCH(F7,StartFile)))

will return the number of the Box.

If you already have the file list set up as you have written, select it's
column, then do Data/Text to Columns with "-" as the separator.


--ron
 
Back
Top