Excel validation dropdown - populate with relevant values based on category

Question

So I have two categories - lets call them 'Cars' and 'Vans'. The first sheet in the workbook has a list of vehicles with the category name in the cell to the left:

CATEGORY    VEHICLE ID
Car         12987
Van         28374
Van         34872
Car         38747
Car         48726

I want to use that list elsewhere in the workbook to populate validation dropdowns. Is there a way to pass a category name (i.e. Vans or Cars), search the left column for cells that match that value, then where there are matches populate the dropdown with values from the adjacent ID column?

Answer 1

Data Validation will only accept ranges, not arrays, and the ranges have to be contiguous, so you'll have to use a helper range or sort by CATEGORY. To replicate the results shown in the images, follow the steps below.

1. Enter this as an array formula in C2 (ctrl+shift+enter) and fill down:

=IF(COUNTIF($C$1:C1,C1)<COUNTIF($A$2:$A$6,C1),C1,INDEX($A$2:$A$6,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$6),0)))

2. Enter this in D2 (again as an array formula) and fill down: =INDIRECT("B"&SMALL(IF($A$2:$A$6=C2,ROW($A$2:$A$6)),COUNTIF($C$1:C2,C2)))

3. Enter this as the Data Validation list formula in G2, then fill G2 down to G3: =INDIRECT("D"&MATCH(F2,$C$2:$C$6,0)+ROW($A$1)&":D"&MATCH(F2,$C$2:$C$6,0)+COUNTIF($A$2:$A$6,F2)+ROW($A$1)-1)