LOOKUP Bates Number Ranges Pages Fall Within

Here's a quick demo of how you can use Excel's LOOKUP formula to find where a list of Bates number falls in a production range.

In column A we have a list of Bates numbers from the middle of documents. We want to find where these pages fall in the Bates number ranges listed in columns F and G. First copy the numbers to look up and ranges into adjacent columns and remove the letter prefixes. Then in column C enter a lookup formula to find the beginning Bates range:

LOOKUP(B2,H:H)

The formula searches for B2 in column H with the beginning numbers.

Then in column D enter a modified version of the formula which includes the ranges for both the beginning and ending numbers:

=LOOKUP(B2,H:H,I:I)

This will give us in columns C and D the ranges in which the list of numbers in column B fall between.