Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question
    
Can you share a doc with us? –  Jacob Jan Tuinstra Feb 14 '13 at 7:23
add comment

2 Answers

up vote 3 down vote accepted

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
share|improve this answer
    
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
add comment

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

share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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