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?

• Can you share a doc with us? Feb 14, 2013 at 7:23

Try this:

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

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) Feb 14, 2013 at 21:47

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

• Upvote for working too. But Onen's is simpler (no SIGN or strange math ;)) Feb 14, 2013 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. Feb 14, 2013 at 21:50

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 = pet;
categories = lures;
categories = hook;
categories = rod;
categories = lines;
categories = sinkers;
categories = swivels;
categories = accessories;
categories = 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";
}
``````