CODEWITHSUNDEEP
excelvba
user3353723
user3353723

Reputation: 219

Simple VlookUp on MS Excel not working

I am trying to use VLookUp from MS Excel 2010 for the data below:

enter image description here

Basically I want to update the Value of CostA by the Value given in Cost by using the Job Ref but ends up #NA

The formula I paste in cell B4: =VLOOKUP(A4,E3:F4,5,FALSE)

NOTE: The Column A Job Ref is a link but even when I remove the hyperlink the same problem occurs. I have attached the Excel file.

Excel file

Upvotes: 1

Views: 117

Answers (2)

user1923975
user1923975

Reputation: 1389

=vlookup(A4,$E$3:$F$4,2,false)

The $ signs fix the range that your formula is looking in. When your doing more lookups (e.g. have more job references in cells A5, A6 etc), you can just paste the formula down (don't forgot to extend your range).

The 2 means when finding that job ref, return the result from the second column of your data set, with your data set being E3:F4. Because in your initial formula that was a 5, the vlookup was finding the job ref, then it's trying to return the 5th column, but E3:F4 is obviously only 2 columns so it'll return an error.

Hopefully this helps.

Upvotes: 1

Calvin
Calvin

Reputation: 443

The Job Ref number is stored as text. Select cell A4 and (a) click the drop down and select "Convert to Number" or (b) go to edit mode by hitting F2 then press Enter.

Also, your VLOOKUP reference is outside your table. Change 5 to 2.

Upvotes: 4

Related Questions