Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
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. |
#3
|
|||
|
|||
Gotcha, thanks. The zipped file is attached.
|
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Quote:
|
#6
|
|||
|
|||
Pleasure was all mine mate :)
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with a Formula I need help with??? | Excel Worksheet Functions | |||
Trouble with formula | Excel Worksheet Functions | |||
Formula trouble | Excel Discussion (Misc queries) | |||
trouble with formula | Excel Discussion (Misc queries) | |||
I'm new at this - having trouble with a formula | Excel Discussion (Misc queries) |