Having trouble with formula
 

Hello all,

I have attached a picture (odd that attached excel file not allowed?) of the formula I've written incorrectly and the data I'm using.

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.

Any help is greatly appreciated. TIA!

Steve

You can attach your excel file if you add it to a zip file first.
If you don't have winzip or similar, just right click the file and chose "add to compressed file" then attach that to your post.

Gotcha, thanks. The zipped file is attached.

Try the below formula in cell C4. Confirm it as an array formula (press Ctrl, Shft & Enter rather than just enter) and copy down.

=INDEX(I$4:I$11,MATCH(1,(E$4:E$11=A4)*(G$4:G$11<B4 )*(H$4:H$11B4),0))

You'll know it's been correctly entered as an array formula when Excel puts curly brackets around the whole formula.

Hope that helps.

S.

You just saved my afternoon. Thank you sir!

Pleasure was all mine mate :)

Having trouble with formula
 

"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)))))