# Extract values based on two (2) criteria

1. ## Extract values based on two (2) criteria

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

2. ## Re: Extract values based on two (2) criteria

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

3. ## Re: Extract values based on two (2) criteria

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

4. ## Re: Extract values based on two (2) criteria

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

5. ## Re: Extract values based on two (2) criteria

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.

6. ## Re: Extract values based on two (2) criteria

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

7. ## Re: Extract values based on two (2) criteria

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))),"")

8. ## Re: Extract values based on two (2) criteria

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

9. ## Re: Extract values based on two (2) criteria

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.

10. ## Re: Extract values based on two (2) criteria

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

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