0

I am using two worksheets in Excel but am having problems with using VLOOKUP.

Sheet 1:-

ID    CATEGORY_ID

Sheet 2:- CATEGORY_ID CATEGORY_NAME

I essentially want to add the CATEGORY_NAME in the 3rd column of sheet 1, so I am using the following:-

=VLOOKUP(C2,Sheet2!A2:A20+Sheet2!A2:B20,2,FALSE)

However, this only returns:-

#VALUE!

There are 17000 rows in worksheet 1. All rows have a category ID. Worksheet 2 only has 20 rows, including the header row (so 19 unique category ID/names). What am I doing wrong here? Driving me insane!

  • 1
    What are you trying to do in parameter 2? You entered "Sheet2!A2:A20+Sheet2!A2:B20", which is a sum of two ranges?? Try only Sheet2!$A$2:$B$20 instead. Note: Add the $ signs to keep the lookup range constant, otherwise, the range will move relative together with your formula copying. – Aganju Dec 14 '15 at 13:05
  • I'm not sure - I tried using Excel's function tool and must have made a mess of it - but it's now working! Thanks! – Grant G Dec 14 '15 at 13:14
2

(made the comment an answer as it solved it) You entered "Sheet2!A2:A20+Sheet2!A2:B20", which is a sum of two ranges?? Try only Sheet2!$A$2:$B$20 instead. Note: Add the $ signs to keep the lookup range constant, otherwise, the range will move relative together with your formula copying

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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