# Need to extract a small range from a large one based on cell value

1. ## Need to extract a small range from a large one based on cell value

Hi there ,

Any body can help me understand how to extract a small range from a big amount of data based on the value of one cell using index function and small function

here is the function but i don't understand it ... please help me to apply it ...

=INDEX(B2:C14,SMALL(IF(B2:B14="match",ROW(B2:B14)-ROW(B2)+1,ROW(B14)+1),"row_item"),"col_index")

2. ## Re: Need to extract a small range from a large one based on cell value

See if this helps.

This is your data in the range B2:C14...

Data Range
 A B C 1 Value Code 2 _____ 98 A 3 _____ 17 A 4 _____ 62 X 5 _____ 51 X 6 _____ 53 A 7 _____ 43 A 8 _____ 45 X 9 _____ 27 A 10 _____ 19 A 11 _____ 77 X 12 _____ 88 A 13 _____ 29 A 14 _____ 68 A

You want to extract the data from B2:B14 that corresponds to "X" in C2:C14. You want the results listed starting in cell E2 and downwards.

Enter this array formula in cell E2:

=IFERROR(INDEX(B:B,SMALL(IF(C\$2:C\$14="X",ROW(C\$2:C\$14)),ROWS(E\$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks (meaning all the data that meets the criteria has been extracted).

Your results should look like this:

Data Range
 A B C D E 1 Value Code Value 2 _____ 98 A _____ 62 3 _____ 17 A _____ 51 4 _____ 62 X _____ 45 5 _____ 51 X _____ 77 6 _____ 53 A _____ 7 _____ 43 A _____ 8 _____ 45 X _____ 9 _____ 27 A _____ 10 _____ 19 A _____ 11 _____ 77 X _____ 12 _____ 88 A _____ 13 _____ 29 A _____ 14 _____ 68 A _____

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1