Excel - filter results in column, need to find adjacent column data

  • Thread starter Thread starter adeater
  • Start date Start date
A

adeater

I have columns of data P3:P1007 and that is filtered for the 5 larges
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 til
AB3:AB1007 are values related to column O3:O1007 which has dates in th
form of 20000809 (9th august 2000). When the 5 largest and 5 smalles
values for P3:P1007 are shown i need a way to display the correspondin
date from O3:O1007 to show up in an adjacent column. Havent been abl
to understand Lookup or Match functions and would appreciate help
 
Hi,

Assuming that your list of 5 largest and smallest values are in Colum
AC,

AD3, copied down:

=IF(N(AC3),INDEX($O$3:$O$1007,MATCH(AC3,$P$3:$P$1007,0)),"")

Hope this helps
 
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 tryin
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 bu
would the MATCH part possible be MATCH(AF3,$O$3:$)$1007,0)),"")

thanks for your hel
 
I checked the formula, with the changes you made...

=IF(N(AF3),INDEX($O$3:$O$1007,MATCH(AF3,$P$3:$P$1007,0)),"")

and it works fine. Make sure you haven't inadvertently added som
other character in the formula -- like a bracket where it doesn'
belong.

Post back if you're still having trouble
 
Hi Domenic. Strangely it still isnt working. I dont know if it makes
different but more info:

The entries in column P from 3 to 1007 are all in the forma
=(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
 
I don't know why the formula is not working for you. If you're stil
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, unles
you're entering the formula incorrectly, I don't know what the proble
could be.

Hopefully some else might be able to help
 
Back
Top