0

I keep getting a return of NA when trying to accomplish the following: formula:

=vlookup(left(Q2,3),A2:C20,3,false)

Definitions:

Q2 is a cell that is populated from a formula. Its calculated value is 111-0021-12345

Column A is a part list which includes the value 111

Column C is a list that associates the column A numbers to supplier names.

If I just manually enter 111 in any cell and use that cell as my "lookup value" the formula returns the correct supplier name. But, when the "Lookup Value" is from a cell that is populated from a formula I get #NA. The lookup value cell IS being populated correctly.

Is there a way to overcome this problem? I am using Windows 7 and Excel 2010.

Improve this question

2 Answers 2

Reset to default
2

LEFT function always returns a text value, try adding zero within the formula to make it numeric....then the VLOOKUP should work as required, i.e.

=VLOOKUP(LEFT(Q2,3)+0,A2:C20,3,FALSE)

Improve this answer
2
  • Does VLOOKUP always require a numeric value for the lookup? Because I swear I've done it without...
    – nhinkle
    Jul 31, 2013 at 0:12
  • 2
    @nhinkle it just needs the lookup_value format to match the table_array format. More info. For this one, it was looking for a text value in a numeric column.
    – Raystafarian
    Jul 31, 2013 at 0:38
1

Barry houdini's identified the problem: for your table, you need a numeric lookup value, but the LEFT function returns text. Another way to fix your formula is to wrap the lookup value in a VALUE() function. This will transform the text inside it to a numeric value.

=VLOOKUP(VALUE(LEFT(Q2,3)),A2:C20,3,FALSE)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .