"steveseer1" wrote:
I have attached a picture (odd that attached excel file
not allowed?)

For the future, you can upload an example Excel file (devoid of any private
data) to any of the free file-sharing websites below. Then post post the
"shared", "public" or "view-only" link (aka URL; http://...) in a response
here. The following is a list of some free file-sharing websites:

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com


"steveseer1" wrote:
What I'm trying to do is write a formula that auto populates
the company (yellow background) based on the date and number
columns of spreadsheet A, using the info from spreadsheet B.
For example, column 5 should populate "IBM" since the date is
1/1/12 and the "Actual #" is between the low and high # range
of IBM. Hope that make sense... :)

For some reason the formula I wrote populates for the first
column only,and I get errors when I drag down.

Too difficult to explain everything you did wrong. Easier just to give you
one implementation that works. You can download "match company.xls" from
https://www.box.com/s/298fg9bzrryhoop5m8da.

If you need error-checking, it is easier to implement by using helper cells,
which you can hide. They are columns C and D in my example. In that case,
your column C is my column E (error-checking) and F (no error-checking); and
your "worksheet B" table in columns E:I is in my columns H:L.

The formula in column E is (E4 for example):

=IF(ISNUMBER(MATCH(A4,$H$4:$H$11,0))=FALSE,"error1 ",
IF(OR(B4<INDEX($J$4:$J$11,C4),B4INDEX($K$4:$K$11, D4)),"error2",
INDEX($L$4:$L$11,MATCH(B4,INDEX($J$4:$J$11,C4):IND EX($J$4:$J$11,D4)))))

"Error1" indicates an invalid date. "Error2" indicates an invalid amount,
namely below the lowest or above the highest for the date.

The formula in C4 (hidden) is the table-relative index of the lowest value
for the date in A4:

=MATCH(A4,$H$4:$H$11,0)

The formula D4 (hidden) is the table-relative index of the highest value for
the data in A4 (note: array-enter the formula by pressing ctrl+shift+Enter
instead of just Enter):

=MATCH(2,1/(A4=$H$4:$H$11))

Note that columns C and D might display Excel errors (#N/A) if "error1" or
"error2" is displayed in column E.

If you do not need error-checking, you can avoid the helper cells in columns
C and D.

The formula in column F is (F4 for example; note: array-enter the formula
by pressing ctrl+shift+Enter instead of just Enter):

=INDEX($L$4:$L$11,MATCH(B4,INDEX($J$4:$J$11,MATCH( A4,$H$4:$H$11,0))
:INDEX($J$4:$J$11,MATCH(2,1/(A4=$H$4:$H$11)))))