If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel - filter results in column, need to find adjacent column data
I have columns of data P3:P1007 and that is filtered for the 5 largest
and 5 smallest values by =IF(ROW()-ROW(AF$3)+1=10,IF(ROW()-ROW(AF$3)+1=5,LARGE($P$3:$P$1007,ROW()-ROW(AF$3)+1),SMALL($P$3:$P$1007,ROW()-ROW(AF$3)+1-5)),"") The problem is that the columns P3:P1007 and Q3:Q1007 and so on till AB3:AB1007 are values related to column O3:O1007 which has dates in the form of 20000809 (9th august 2000). When the 5 largest and 5 smallest values for P3:P1007 are shown i need a way to display the corresponding date from O3:O1007 to show up in an adjacent column. Havent been able to understand Lookup or Match functions and would appreciate help. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Excel - filter results in column, need to find adjacent column data
Hi,
Assuming that your list of 5 largest and smallest values are in Column AC, AD3, copied down: =IF(N(AC3),INDEX($O$3:$O$1007,MATCH(AC3,$P$3:$P$10 07,0)),"") Hope this helps! --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Excel - filter results in column, need to find adjacent column data
Hi Domenic,
Unfortunately i keep getting"the formula you typed contains an error) The column with the 5 greatest and smallest data is AF and i'm trying to get the result in AE hence i changed your formula to: =IF(N(AF3),INDEX($O$3:$O$1007,MATCH(AF3,$P$3:$P$10 07,0)),"") do i need to make any other changes? and excuse me for my ignorance but would the MATCH part possible be MATCH(AF3,$O$3:$)$1007,0)),"") thanks for your help --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Excel - filter results in column, need to find adjacent column data
I checked the formula, with the changes you made...
=IF(N(AF3),INDEX($O$3:$O$1007,MATCH(AF3,$P$3:$P$10 07,0)),"") and it works fine. Make sure you haven't inadvertently added some other character in the formula -- like a bracket where it doesn't belong. Post back if you're still having trouble. --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
Excel - filter results in column, need to find adjacent column data
Hi Domenic. Strangely it still isnt working. I dont know if it makes a
different but more info: The entries in column P from 3 to 1007 are all in the format =(C4-C3)-(B4-B3) for P3 and =(C1007-C1006)-(B1007-B1006) for P1006. O3 is the date 20000809 (9th aug 2000), down to 1007 (4 years). AF3 to AF12 has 10 rows of data taken from P3 to P1006. I've double checked for unwanted characters but cant see any. Any further advice? --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
Excel - filter results in column, need to find adjacent column data
I don't know why the formula is not working for you. If you're still
getting the same error -- "The formula you typed contains an error" -- then, as far as I know, that means that the syntax is incorrect. But, as I previously mentioned, the syntax is correct. So, unless you're entering the formula incorrectly, I don't know what the problem could be. Hopefully some else might be able to help. --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
decipher log of scanpst.exe | km | General Discussion | 0 | July 18th, 2004 09:00 AM |
Countif with 2 or more data ranges in same column | Doug | Worksheet Functions | 1 | July 4th, 2004 08:57 AM |
Word mail merge with Excel data | Peter Jamieson | Mailmerge | 1 | April 26th, 2004 07:30 PM |
Linking Excel data through Access to use a Form as an Interface | Laura | Links and Linking | 0 | March 23rd, 2004 03:59 PM |