Having trouble with formula

[steveseer1]

Hello all,

I have attached a picture (odd that attached excel file not allowed?) o
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 compan
(yellow background) based on the date and number columns of spreadshee
A, using the info from spreadsheet B. For example, column 5 shoul
populate "IBM" since the date is 1/1/12 and the "Actual #" is betwee
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!

Stev

+-------------------------------------------------------------------
|Filename: exampleCombo.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=643
+-------------------------------------------------------------------

 

[Spencer101]

Hello all,

I have attached a picture (odd that attached excel file not allowed?) o
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 compan
(yellow background) based on the date and number columns of spreadshee
A, using the info from spreadsheet B. For example, column 5 shoul
populate "IBM" since the date is 1/1/12 and the "Actual #" is betwee
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 chos
"add to compressed file" then attach that to your post

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

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
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

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),B4>INDEX($K$4:$K$11,D4)),"error2",
INDEX($L$4:$L$11,MATCH(B4,INDEX($J$4:$J$11,C4):INDEX($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)))))