1

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

4

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
  • Works like a charm. Mine is actually: =IFERROR(VLOOKUP(C725,Categories!A:B,2,FALSE),"") (using the whole columns) – Gui13 Feb 14 '13 at 21:47
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

  • 1
    Upvote for working too. But Onen's is simpler (no SIGN or strange math ;)) – Gui13 Feb 14 '13 at 21:47
  • If you have 100 rows, one formula would suffice. In the other case, you would have to copy the formula each time. – Jacob Jan Tuinstra Feb 14 '13 at 21:50
0

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";
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.