CODEWITHSUNDEEP
excelvba
stackErr
stackErr

Reputation: 4170

Excel VLOOKUP N/A error

I have a table setup like this:

enter image description here

I am trying to do a lookup, where Column D value matched one of the Column A values and returns Column C value.

The numbers in column A and D are stored as text.

My formula is VLOOKUP(F3,A1:C3,3,TRUE) but this returns "Value not available error". What is wrong with the formula?

EDIT

Figured out that some of the values were stored as general.

Now the problem is that I have to get an exact match with leading zeroes. For example in Column D I have "27154" but in Column A I have "000027154", these should match.

But if I have "000271540" or any other variant in Column A, it should not match.

All the numbers in Column A are 9 digits long with leading zeroes where needed.

Upvotes: 1

Views: 5516

Answers (3)

Skip Intro
Skip Intro

Reputation: 860

You can use wildcards in VLOOKUP:

=VLOOKUP("*"&F3,A2:C3,3,FALSE)

Upvotes: 4

shahkalpesh
shahkalpesh

Reputation: 33474

VLOOKUP(TEXT(F3, "000000000"),A1:C3,3,FALSE)

It will require creating the same value for the VLOOKUP to find the value. Looking at your example, the length of the text in column A is 9 characters. As a result, the padding is applied which will be used to search. To make it exact match, FALSE is used as last argument to VLOOKUP.

Upvotes: 4

Joseph
Joseph

Reputation: 5160

There's nothing wrong with the formula. The problem is that the value in A2 is text and treated as text when comparing to the number in F3.

If you can't change your values in column A, then you can use this array formula:

=SUM((F3=VALUE(A2:A3))*(C2:C3))

Enter with CTRL+SHIFT+ENTER

This will convert the values in A2:A3 as numbers for the comparison against F3.

Upvotes: 2

Related Questions