title: Excel : populate data validation dropdown list using a single formula that gets multiple values from lookup with multiple criteria
body: <p>I've lost a day scouring the net for a solution. So any help would be MUCH MUCH appreciated.</p>
  
  <p>I need to be able to dynamically populate a data validation drop down list with values obtained from the results of a lookup formula. Is that possible?</p>
  
  <p>I need 2 such drop down boxes for each of 700 rows.</p>
  
  <h1>Sample Data:</h1>
  
  <p>Color and size data is to be pulled FROM this sheet:    </p>
  
  <p><br></p>
  
  <h2>COST PRICES SHEET</h2>
  
  <h3>BRAND &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CODE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ACTUAL COLOR &nbsp;&nbsp;&nbsp;&nbsp;ACTUAL SIZE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COST</h3>
  
  <p>TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COL. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;S,M,L,XL,XXL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.26<br>
  TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COL. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XXXL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.36<br>
  TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHT. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;S,M,L,XL,XXL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.26<br>
  TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHT. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XXXL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.36<br>
  RUSSELL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RTA09 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Colors 
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;S-XXL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.44<br></p>
  
  <p><br></p>
  
  <p>and to be used as values in dropdowns in cells marked [*1] and [*2] in the following worksheet :                </p>
  
  <h2>SALES PRICES SHEET</h2>
  
  <h3>BRAND &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CODE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COLOR &nbsp;&nbsp;SIZE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COST  &nbsp;&nbsp;&nbsp;&nbsp;ACTUAL COLOR &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ACTUAL SIZE</h3>
  
  <p>TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cols 
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;S-XXL 
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[?]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*1]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*2]
  <br>
  TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cols 
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3XL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[?]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*1]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*2]
  <br>
  TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;White
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;S-XXL 
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[?]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*1]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*2]
  <br>
  TOPSPORT &nbsp;TTA01 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;White
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3XL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[?]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*1]
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[*2]
  <br></p>
  
  <p><br></p>
  
  <pre><code>    So, according to the above example...
      Cells marked [*1] should house a dropdown with options of "WHT." and "COL."
      Cells marked [*2] should house a dropdown with options of "S,M,L,XL,XXL" and "XXXL"
      Cells marked [?] will use these dropdown values in a lookup formula 
                       to pull in the Cost Price from COST PRICES SHEET.
  </code></pre>
  
  <h2>THE QUESTION...</h2>
  
  <p>Is there some formula I could use in the Data Validation formula box which can do this?
  If not, is there any other way to populate the dropdowns?</p>
  
  <p><br></p>
  
  <h2>FURTHER READING</h2>
  
  <h2>WHY IS ALL THIS NECESSARY?</h2>
  
  <p>Because the COST PRICES SHEET contains Price information from dozens of different suppliers, each of which have different ways of saying the same thing (eg for "White","WHIT", "WHIT.", "Wht","WH" etc). Hence a simple lookup from the SALES PRICES SHEET is not possible, as the sales sheet uses a standard string to define a term (eg "White") which would fail to find the various spellings used by the different suppliers.</p>
  
  <p>So, if the specific values used by each supplier for all color /size options for a product were available in the SALE PRICES SHEET, the user could then select the color and size option which applies to that record, thereby enabling a lookup formula to use those values to pull the appropriate cost price from the COST PRICES SHEET into the COST column of the SALES PRICES SHEET.</p>
  
  <p>And with around 5000 lines of Cost Prices being updated every month, it's not feasible to manually  standardise the various spellings used. </p>
  
  <h2>Download actual Excel file from <a href="https://drive.google.com/file/d/0B3Ri4ZKo6Yb8cjd5WExQZUVvNlU/edit?usp=sharing" rel="nofollow">HERE</a></h2>
  
  <p>(ignore the 'Settings Sheet' in the download file).</p>