Google Sheets – How to Multiply Cell Value Based on Keyword

You need to use the VLOOKUP function

I have the following in Sheet1

And this in Sheet 2

I have assigned a range to the values in Sheet2 and called the range LookupValues.

Then in my formula for column D in sheet 1 I have:

A breakdown of the formula is:

• A3 is the value to find in the lookup range
• LookupValues a range to search through
• 3 the index of the range to return if the value (A3) is found in the range (LookupValues)

UPDATE

To create a range you right click the sqaure in the top left of the spreadsheet and select Define named range

You then enter the nickname or alias you want the range to be known/referenced as and the range of cells you want to be available in the range.

You can then access the range of cells by the name rather than the traditional Sheet2!A1:T100 method.

EDIT 2

In response to your updated question you will need to change the formula to this:

=VLOOKUP(B1, LookupValues, 1)

This will search through your range for the value in B1 from Sheet1. e.g "User A" and then return whatever value is in column 1 e.g "ABC"

Complex conditional formatting can be achieved in Google Spreadsheets using Google Apps Script. For example, you could write a function that changes the background colour of an entire row based on the value in one of its cells, something that I do not think is possible with the "Change color with rules" menu. You would probably want to set triggers for this function such as "On Edit", "On Open" and "On Form Submit".

Documentation for setBackgroundRGB() function

UPDATE: Here is a Google Apps Script example of changing the background color of an entire row based on the value in column A. If the value is positive, use green. If empty, white. Otherwise, red. See the results in this public Google Spreadsheet. (You will have to be signed in for the script to run, but without signing in you can still see results).

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, 3);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] > 0){
    dataRange.setBackgroundRGB(192, 255, 192);
  }else{
    dataRange.setBackgroundRGB(255, 192, 192);
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 2) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}

​