# Extract Data From List Dependant on cell criteria

#### ExcelRoy

##### Well-known Member
Hi,

I am looking for a formula to extract data from a list on another sheet

Sheet1

D7 has a alpha numeric job reference

A13: Part Number
B13: Description
C13: Quantity
D13: Cost
E13: Order Date

I need this to show every entry in the main list on Sheet2 which can always change in terms of lines per job

Any help appreciated

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### FDibbins

##### Well-known Member
I think you need to show some sample data, rather than just headings, along with your criteria, and some expected results.

You probably need to use something like the INDEX/SMALL/IF array formula

##### MrExcel MVP
Care to post a small sample from the source data? Would you also clarify "D7 has a alpha numeric job reference"
since the headers you posted does not contain any reference to job numbers.

#### ExcelRoy

##### Well-known Member
Hi, sorry, I will try and add some detail

My data on sheet 1

A13: Part Number
B13: Description
C13: Quantity
D13: Cost
E13: Order Date
F13: Job Reference (Alpha Numeric)

I would like sheet 2 to show each row of data from the job reference in D7

ie, Part Number, Description, Quantity, Cost, Order Date, from A1,B1,C1,D1 etc

Many thanks

##### MrExcel MVP
Sheet2

D7 contains a job reference of interest.

In E7 just enter:

=COUNTIFS(Sheet1!F14:F400,D7)

Row 8 of Sheet2 houses the following headers:

A8: Idx, B8: Part Number, C8: Description, D8: Quantity, E8: Cost, and F8: Order Data.

In A9 control+shift+enter, not just enter, and copy down:

=IF(ROWS(\$A\$9:A9)>\$E\$7,"",SMALL(IF(Sheet1!\$F\$14:\$F\$400=\$D\$7,ROW(Sheet1!\$F\$14:\$F\$400)-ROW(Sheet1!\$F\$14)+1),ROWS(\$A\$9:A9)))

In B9 just enter, copy across, and down:

=IF(\$A9="","",INDEX(Sheet1!\$A\$14:\$F\$400,\$A9,MATCH(B\$8,Sheet1!\$A\$13:\$F\$13,0)))

Replies
13
Views
607
Replies
3
Views
156
Replies
2
Views
476
Replies
7
Views
3K
Replies
3
Views
496

1,182,032
Messages
5,933,357
Members
436,889
Latest member
vamsi nandan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back