Sheets IF Function setup and/or formatting errors

Question

Cell C5 on the Trip Assignment Form tab is a dropdown selection list of destinations. I am trying to make sheets automatically fill in the C6 cell with the matching address (coming from a tab called Schools). When I put the below formula in I get an error or FALSE but I cannot figure out why.

=if(C5=Schools!A1, C6=Schools!B1, "Other Error")

I need to make the formula work for any destination selected so that the appropriate address is inserted into the cell. Can I do this like the way I am thinking or would Apps Script be better and if so how would I go about that?

"Trip Assignment Form"

"Schools" data sheet

Link to copy of my sheet:

https://docs.google.com/spreadsheets/d/1Xkwxjtf6syEd-zGaPUVsyE2_6Kfczu8c-8W3RuRg9CY/edit?usp=sharing

If you'll provide an image of your spreadsheet I'd be glad to assist you. I apologize but I don't like to follow links off of this site — Cooper
@Cooper - I just added two photos, please let me know if you need more. — Kyle D
I don't use formulas very much but I'm pretty sure that this is an easy use of vlookup. So you can take a look at it but some other volunteer will come around some time today and teach us both something. — Cooper

Cooper Cooper · Accepted Answer · 2020-02-17T19:49:06

It is possible to do with Google Apps Script but it's probably not the easiest way to go

You can use the onEdit trigger and this sort of code:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=='Sheet12' && e.range.columnStart==1 && e.range.rowStart==1 & e.value!='') {
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('Sheet12');
    var v1=sh1.getRange(1,1).getValue();
    var sh2=ss.getSheetByName('Sheet13');
    var v2=sh2.getRange(1,1,sh2.getLastRow(),2).getValues();
    for(var i=0;i<v2.length;i++) {
      if(v2[i][0]==v1) {
        sh1.getRange(2,1).setValue(v2[i][1]);
      }
    }
  }
}

Animation:

Your setup:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=='assignment sheet name' && e.range.columnStart==3 && e.range.rowStart==5 & e.value!='') {
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('assignment sheet name');//assignment sheet name
    var v1=sh1.getRange(e.range.rowStart,e.range.columnStart).getValue();
    var sh2=ss.getSheetByName('schools sheet name');//schools sheet name
    var v2=sh2.getRange(1,1,sh2.getLastRow(),2).getValues();
    for(var i=0;i<v2.length;i++) {
      if(v2[i][0]==v1) {
        sh1.getRange(6,3).setValue(v2[i][1]);
      }
    }
  }
}

Sheets IF Function setup and/or formatting errors

2 Answers