Is there a formula for this?

[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!

 

[Frank Kabel]

Hi
use the following set-up (assumption: the file numbers are sorted
ascending):
A B C
1 01245 821 873
2 01236 874 900
.....
Note: separate the two boundarie in to separate cells

Now use the following formula (assumption: cell E1 contains the file
number to search for):
=INDEX(A1:A20,MATCH(E1,B1:B100,1))

 

[Peo Sjoblom]

The problem with that approach (if indeed that's the way it's setup) is that
821-873 is text,
you can use a lookup formula but then you should just use

821 01245
874
start of next range

=LOOKUP(A1,B3:B4,C3:C4)

so if the value is 851 it will return 01245, don't use 821-873

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom