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? – Jacob Jan Tuinstra Feb 14 '13 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) – Gui13 Feb 14 '13 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 ;)) – 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