Categorize cells in Google Spreadsheets

Question

I'm using Google Spreadsheets to manage my shared expenses with my girlfriend. It looks like this:

| Who   | What       | Category   | When         | Amount |
| Gui13 | Vegetables | Meals      | October 2012 | 26€    |
| GF    | AT&T       | Multimedia | October 2012 | 30€    |
...

Now, I'd like to categorize automatically the expenses, meaning autofill the "Category" column according to what's in the "What" column.

I have an extra sheet where I have 2 columns indicating which value should be categorized as this or that:

| What       | Category   |
| Vegetables | Meals      |
| Wine       | Meals      |
| AT&T       | Multimedia |
...

What I want to do is:

• find if the "What" cell content is found in the 2nd sheet
• if yes, fill the category with the content of the cell of the 2nd sheet
• if no, nothing

I couldn't figure how to do that with GDocs. I think it would involve FILTER or INDEX, but I'm not sure. Any idea?

Example sheet: https://docs.google.com/spreadsheet/ccc?key=0AhTjRwDZNsNNdF90Ymx0clpCV0l1ckhscnBmRVRNU0E&usp=sharing

Answer 1

Try this:

=iferror(vlookup(C2,Sheet2!A$1:B$6,2,FALSE),"")

Here's an example.

Some notes:

• Put the formula in the cell you want to fill
• C2 refers to the value you're using to look up in the array
• When entering the array range, make sure to put in $ before the row numbers, otherwise the range will be dynamic, not fixed.
• If you add more categories, you'll need to update the formula on Sheet1

Answer 2

If you use the following formula, calculation will be done automatically:

=ARRAYFORMULA(VLOOKUP(D2:D;Categories!A1:B8;{2}*SIGN(ROW(D2:D));0))

The SIGN and ROW combination will iterate through the column range and the VLOOKUP will provide the correct find.

I've copied your file: automatic vlookup

Answer 3

This is JavaScript for google sheets. This will return a category based on cell contents. Forgive my bad spelling.

/*
-= Categorie =-
*/
var pet = ["dog", "pet", "cat"];
var lures = ["lure"];
var hook = ["hook"];
var rod = ["rod"];
var lines = ["weave","line"];
var sinkers = ["sinker"];
var swivels = ["snap", "swivel"];
var accessories = ["hoodie", "Necklace", "Sunglasses", "T-shirt", "hat", "bag"];
var other = ["Warmer", "CREAM", "Manicure", "Usb", "mug", "drinkware", "phone", "case"]; //unmatched

/*
-= Categories =-
*/
var categories = [];
categories[0] = pet;
categories[1] = lures;
categories[2] = hook;
categories[3] = rod;
categories[4] = lines;
categories[5] = sinkers;
categories[6] = swivels;
categories[7] = accessories;
categories[8] = other;
var categorie = ["pet", "lures", "hook", "rod", "lines", "sinkers", "swiveles", "accessories", "other"];

/*
Function for categorizing items based on text found
@customFunction
*/
function Categorize(value) {
  stringtosearch = value;
  //loop throgh each category until a match is reached then return the category
  for(var i = 0; i < categories.length; i++) {
    for(var j = 0; j < categories[i].length; j++) {
      stringtosearch=stringtosearch.toLowerCase()
        var found = stringtosearch.search(categories[i][j].toLowerCase());
      if(found>-1){
        return categorie[i];
      }
    }
  }
  //No keywords found so it goes into other    
  return "other";
}