VLOOKUP Formula

Download starting Sheet

Now, it has occurred to me that I can save time when entering future expenses because I have realised that I can pretty much categorise the expenses.

• Eating out is unnecessary
• (most) computer expenditure is necessary

...so instead of populating the "Necessary?" field myself, I can use a formula to guess, then type over the formula if I disagree with the categorisation.

Steps to use the VLOOKUP formula:

1. In J3, type: "Expense Type"
2. In K3, type: "Necessary?"
3. In J4, type: "Eating Out"
4. In J5, type: "FALSE"
5. In K5, type: "TRUE"
6. Select column C
7. Choose Data menu on your ribbon
8. Select "Data Validation"
9. Choose "Any Value" to turn off the data validation we added earlier.
10. In C2 type: =VLOOKUP(B2,$J$3:$K$5,2,FALSE)
11. Copy this formula down.

Note:

• The formula uses the table to populate the cells.
• The other advantage of using this formula is that if you want to change the categorisation of all entries for "Eating Out", you only need to change it in one place: cell K4  and recalculate.
• If you add Expense types, you will need to change the formula appropriately.
  • so adding one expense type in J6, would mean that you need to change the formula in C2 to:   =VLOOKUP(B2,$J$3:$K$6,2,FALSE)   then copy it down.
  • Alternatively, you could use =VLOOKUP(B2,$J$3:$K$500,2,FALSE)  to allow 500 or so expenses to be added, and then you never have to change the formula again.

Your sheet should now look like this

Next Lesson: Teach Yourself Excel Lesson 23 - Find And Replace

Training Video on the VLOOKUP Formula in Excel: