Extract values based on two (2) criteria

**bjnockle** · 09-07-2015, 03:14 PM

Need a formula to extract values in column A through D if criteria is met (2 criteria). There is a List Sheet that houses the names and state. Actual data is in Data sheet. Headings are Sales Person, Region, Products and Price. Would like a formula to extract the right values when a sales person (in G2) is selected from the dropdown and the state selected (in H2). For example, G2: Vergie, Nicholes and H2: Indiana.

Also, need a second formula to extract unique names and the average sales price (see desired outcome in M4:N9.

See attached file.

Sample I

Sales Person Region Products Price
Vergie, Nicholes * North Peanuts $25
Vergie, Nicholes * North Peanuts $41
Vergie, Nicholes * North Peanuts $13
Vergie, Nicholes * North Peanuts $15
Vergie, Nicholes * North Peanuts $32
Vergie, Nicholes * North Peanuts $38
Vergie, Nicholes * North Peanuts $11
Vergie, Nicholes * North Peanuts $52
Vergie, Nicholes * North Peanuts $33
Vergie, Nicholes * East Peanuts $40
Vergie, Nicholes * South Peanuts $29
Vergie, Nicholes * South Peanuts $7
Vergie, Nicholes * South Peanuts $39
Vergie, Nicholes * South Peanuts $2
Vergie, Nicholes * South Peanuts $53

Sample II

Sales Person Avg. Sales
Anisha, Frum * $13
Dedra, Irving * $43
Eugena, Mims * $47
Illa, Rhem * $51
Vergie, Nicholes * $29

**JohnTopley** · 09-07-2015, 03:33 PM

You say state but you show region? State is not available in "Data".

**Ace_XL** · 09-07-2015, 03:36 PM

In G5
=IF(ROWS($A$1:$A1)>COUNTIF($A$2:$A$79,$G$2),"",INDEX(A$2:A$79,SMALL(INDEX(($A$2:$A$79=$G$2)*(ROW($A$2:$A$79)-ROW($A$2)+1),),COUNTIF($A$2:$A$79,"<>"&$G$2)+ROWS($A$1:$A1))))
Copy down and across as far as you need

In M5
=IF(ROWS($A$1:$A1)>COUNTIF(State,$H$2),"",INDEX(List,SMALL(INDEX((State=$H$2)*(ROW(State)-ROW($A$2)+1),),COUNTIF(State,"<>"&$H$2)+ROWS($A$1:$A1))))

In N5
=IF(M5="","",IFERROR(AVERAGEIF($A$2:$A$79,M5,$D$2:$D$79),0))
Copy down as far as you need

**JohnTopley** · 09-07-2015, 03:52 PM

In G5 copy across and down

=IFERROR(INDEX(A$2:A$79,SMALL(IF($A$2:$A$79=$G$2,ROW(Data!$A$2:$A$78)-ROW(Data!$A$2)+1,""),ROWS(Data!$A$2:A2))),"")

Enter with Ctrl+Shift+Enter

in M5

=IFERROR(INDEX(List!$A$2:$A$21,SMALL(IF(State=Data!$H$2,ROW($A$2:$A$21)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

in N5

=IFERROR(AVERAGEIF($A$2:$A$79,$M5,$D$2:$D$79),"")

Copy down

See attached

**bjnockle** · 09-07-2015, 04:10 PM

Ace_XL: solution works but the state dropdown (H2)and Salesperson dropdown in G2 criteria are only extracting values in A:E. When both dropdowns are selected, the values should be extracted according. Only the Salesperson dropdown is working. Have added state in column E for the pull. See attached file. a new dropdown has been added in M2 for the average. M2 dropdwon should be used to extract the unique names and associated values. See attached file.

**JohnTopley** · 09-07-2015, 04:25 PM

State was not present in your first file! Only required for table 2: You are confusing matters!

**JohnTopley** · 09-07-2015, 04:31 PM

To select by state (Not included in your first file!)

=IFERROR(INDEX(A$2:A$79,SMALL(IF(($A$2:$A$79=$G$2)*($E$2:$E$79=$H$2),ROW(Data!$A$2:$A$78)-ROW(Data!$A$2)+1,""),ROWS(Data!$A$2:A2))),"")

**bjnockle** · 09-07-2015, 04:36 PM

JohnTopley: sorry for the confusion. I added state after I realized it is need for the two dropdown options to work. Thanks

**JohnTopley** · 09-07-2015, 04:44 PM

You only need to put State in H2: it is redundant in M2.

Have we provided the solution you require: if so can you mark the thread as solved.

Thank you.

**bjnockle** · 09-07-2015, 04:58 PM

JohnTopley: works like a charm. Thanks a million JohnTopley.

Extract values based on two (2) criteria

LinkBack

Thread Tools

Rate This Thread

Display

Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Re: Extract values based on two (2) criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract values based on three (3) criteria

Extract values based on two (2) criteria

[SOLVED] Extract values based on two (2) criteria

[SOLVED] Extract duplicated values based on an extra criteria

[SOLVED] Extract Values From One Column Based on Two Criteria From Two Other Columns

[SOLVED] Extract unique values out of list based on an extra criteria

Extract Unique Values from an Array based on Criteria

Bookmarks

Bookmarks

Posting Permissions