# Extract multiple data from multiple column with one criteria

1. ## Extract multiple data from multiple column with one criteria

hi,
extract data from sheet(all) to sheet(invoice) based on criteria sheet(invoice").range(c18) according to sheet(all).range(a2:a1000)

sample uploaded

2. ## Re: Extract multiple data from multiple column with one criteria

In B26

=IFERROR(INDEX(ALL!\$B\$5:\$B\$1000,SMALL(IF(ALL!\$A\$5:\$A\$1000=\$C\$18,ROW(ALL!\$B\$5:\$B\$1000)-ROW(\$A\$5)+1,""),ROWS(\$A\$5:A5))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down

You should now be able to complete all the other columns.

3. ## Re: Extract multiple data from multiple column with one criteria

=IFERROR(AGGREGATE(15,6,ROW(ALL!\$A\$5:\$A\$8)/(ALL!\$A\$5:\$A\$8=\$C\$18),A26)-4,"") s and INDEX s

4. ## Re: Extract multiple data from multiple column with one criteria

Thank you Jhon
just few questions
1 = why you use from row 5 (ALL!\$B\$5:\$B\$1000) while i need to start from row 2 = ALL!\$B\$2:\$B\$1000,
2 if i change range to ALL!\$B\$2:\$B\$1000 then what i change in this part -ROW(\$A\$5)+1,""),ROWS(\$A\$5:A5))) and what is the function of this part

5. ## Re: Extract multiple data from multiple column with one criteria

should be

=IFERROR(INDEX(ALL!\$B\$3:\$B\$1000,SMALL(IF(ALL!\$A\$3:\$A\$1000=\$C\$18,ROW(ALL!\$B\$3:\$B\$1000)-ROW(\$A\$3)+1,""),ROWS(\$A\$3:A3))),"")

my error!!!!

6. ## Re: Extract multiple data from multiple column with one criteria

... I noticed Tim did the same (your posted file was positioned at row 5 so we both took that as the start!)

7. ## Re: Extract multiple data from multiple column with one criteria

thank you john
its working perfectly

and tim you too

8. ## Re: Extract multiple data from multiple column with one criteria

Originally Posted by tim201110
=IFERROR(AGGREGATE(15,6,ROW(ALL!\$A\$5:\$A\$8)/(ALL!\$A\$5:\$A\$8=\$C\$18),A26)-4,"") s and INDEX s
this is not working if i change
range a2:a1000

9. ## Re: Extract multiple data from multiple column with one criteria

Originally Posted by HaroonSid
this is not working if i change
range a2:a1000
working now

thank you

10. ## Re: Extract multiple data from multiple column with one criteria

With Tim's: range should be A3:A1000 not A2: all formulae need to be changed accordingly and will work.

Row 2 is header row, not first data row,

11. ## Re: Extract multiple data from multiple column with one criteria

Dear Harron, Firstly remove merged cell.
In "B26"
Formula:
`Please Login or Register  to view this content.`
copy across.
File attach.

#### Thread Information

##### Users Browsing this Thread

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