Lookup data based on drop down and secondary column google sheets

Question

I'm trying to get data to appear based on a drop down and a secondary column. Here's the sample sheet: https://docs.google.com/spreadsheets/d/1LgHrze7bp0Epfw273Ylx3sVW98VDyd0sSQ1lYmZJUL4/edit?usp=sharing

I'm trying to get the Data sheet info to appear under the dropdown in the Worksheet sheet.

Any help would be great! Thanks!

Ed Nelson Ed Nelson · Accepted Answer · 2017-02-22T14:00:02

Use hlookup like this. Put in B2 of Worksheet:

=transpose(arrayformula(hlookup(B1,Data!B1:G8,{2,3,4,5,6,7,8},false)))

If you put the cities in the rows, it would be much easier for you to add new cities. You can set the dropdown data validation to A2:A and the lookup to A2:H. You can then add cities without adjusting anything. The formula the uses vlookup like:

=transpose(ARRAYFORMULA(vlookup(A1,RData!A2:H,{2,3,4,5,6,7,8},false)))

You can also do it with Google Apps Script that will expand to the number of cities and categories you have. I have added this to the shared sheet on the Script tab. It uses the RData tab as its source.

function onEdit(event) {
  var sheet = event.source.getActiveSheet().getName()//get the sheet name
  if(sheet=="Script" ){//if sheet name is Script
  var ss= SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getSheetByName("RData")//get the data sheet
  var lr=s.getLastRow()//get the last row with data(city)
  var lc=s.getLastColumn()//get the last column with data (category)
  var rng=s.getRange(1, 1, lr, lc).getValues()//get the values
  var dd= s.getRange("Script!A1").getValue()//get the selected city from the dropdown
  var val=[]
  var cat=[]
  for(i=0;i<rng.length;i++){
     if(dd==rng[i][0]){
       for(j=1;j<rng.length+1;j++){
         var t=rng[i][j]
         val.push([rng[i][j]])
         cat.push([rng[0][j]])
       ss.getSheetByName("Script").getRange(1, 2,  val.length,1).setValues(cat) //write the category values
      ss.getSheetByName("Script").getRange(1, 3, val.length,1).setValues(val) //write the numbers
       break //quit when the city is processed
  }}}}}

Attached is a shared spreadsheet the shows all ways of solving your problem:

https://docs.google.com/spreadsheets/d/1h3kYpBTK8OpSVL5PGwQzYsFbIHmCaKx0G5Y8FXSdHH8/edit?usp=sharing

Lookup data based on drop down and secondary column google sheets

1 Answers