Function for conditional sorting of data

**daniel24** · 04-26-2010, 08:39 PM

I had a question regarding a fix I am in while trying to analyze a data. Here's what I am lookin for.

Column A Column B Column C
14 6331
14 8164
14 4731
14 6416
14 8106
15 4764
15 6218
15 6256
15 6372
16 6422
16 4829
16 4941
16 4963
17 6212
17 6371
18 6582
18 6642
18 8143
19 6349
19 6611
19 4710
20 4797
20 4815
20 4831
20 6217
14 6227
14 6364
14 6383

If I put in a value,(say 14) in cell C1, I want all the values in column B corresponding to 14,15,and 16 (in column A) in column D. I think it has something to do with index and match with an if function. It would be really helpful if someone can help me with a function which gives me the desired result. Please find the attached spreadsheet for your reference.
Thank you in advance.

**NBVC** · 04-26-2010, 09:24 PM

So is the criteria anything that is less than or equal to the value you enter in C1 +3?

If so,

in C2, enter:

=IF(A2+0<=$C$1+3,COUNT(C$1:C1),"")

copied down

Then in D1 enter:

=MAX($C$2:$C$285)

in D2:

=IF(ROWS($A$1:$A1)>$D$1,"",INDEX($B$2:$B$285,MATCH(ROWS($A$1:$A1),$C$2:$C$285,0)))

copied down as far as you need.

Adjust ranges to suit.

**daniel24** · 04-26-2010, 09:37 PM

Thanks a ton. This is exactly what I was looking for. I really appreciate your quick reply. Thanks again.

**daniel24** · 04-26-2010, 10:15 PM

Hi there,

Thank you for all your help. I have one more question which might seem to be a little stupid. What if I want to pull out data with some other criteria. Say if I put 17 in C1, I want all the values in Column B corresponding to 15,16,and 17.

Thanks in advance.