vba - Vlookup Excel, Referencing A different Sheet

Question

I am trying to create a simple VLOOKUP function for my spreadsheet using the below: In the first sheet

=VLOOKUP("Salary",'December 2015_natwest_download'!$D$4:$E$43,1,FALSE)

This is the sheet i am trying to reference:

The sheet I am trying reference:

Value          Category
======= ==========
￡530.00        Charlotte Owing
-￡53.00        Gym
-￡16.47        Water
-￡67.00        Phone
-￡11.01        Presents
-￡14.40        Eating out
-￡100.00       Food
-￡65.00        Other

But when I put the VLOOKUP code into my excel, it returns NA. Can anyone see what is causing the error?

Answer 1

The VLOOKUP function is designed to lookup a value on the far left of a block of data and return a corresponding value from a column to the right.

If you need to lookup a value and return a value from a corresponding column to the left of the lookup column, you need to use an INDEX/MATCH function pair.

If you are returning numbers based on a condition (either in that column or another column) either the SUMIF or SUMIFS function will do. Individual entries can be easily collected but if there is more than a single match to your condition, you will receive a sum total of the matching numbers.

????????

The formulas in E4:F4 are,

=INDEX('December 2015_natwest_download'!A:A, MATCH(D4, 'December 2015_natwest_download'!B:B, 0))
=SUMIFS('December 2015_natwest_download'!A:A,'December 2015_natwest_download'!B:B, D4)

Note that the SUMIFS in F5 is returning two Gym entries.