4

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?

Improve this question
13
  • 1
    Firstly, "December" is misspelt not sure if this is just in this question or whether it is in the formula on the workbook but might be worth checking. Secondly, do you have "Salary" as a named range? Commented Jan 18, 2016 at 11:09
  • 1
    =VLOOKUP(LookupValue,LookupTable, columnindex,MatchType) Commented Jan 18, 2016 at 11:19
  • 1
    If "Salary" is a named range you don't need the speech marks around the word. Also, you've specified the column that you want the values of as column 1 (which would be Column D in the range you've stated). If the Salary cannot be found in Column D the formula will produce #N/A. Commented Jan 18, 2016 at 11:20
  • 1
    Your lookup_table refers to $D$4:$E$43 which is 40 rows, but you only have 8 rows of sample data. Is your sample data in Columns D and E? To use vlookup, the value your looking up needs to be to the left of the value you're hoping to return. Commented Jan 18, 2016 at 11:23
  • 2
    If the lookup column is to the right of the data to be returned, you need an INDEX/MATCH function pair, not a VLOOKUP function. VLOOKUP can only return data that is to the right of the column to lookup. Commented Jan 18, 2016 at 11:48

1 Answer 1

Reset to default
4

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.

        index_match_salary

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect! Thanks for the help :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.