Vlookup returns #NA

[jewellove]

I'm confident that the formula is correct =VLOOKUP(B3;Sheet2!B2:C1096;1;FALSE) but it does not return the right answer.. Attached is the document for reference. Thank you

[bebo021999]

VLOOKUP must search for the value in left-column, then returns value in right columns, so in this example must use INDEX/MATCH:

Please Login or Register  to view this content.

[Steve N.]

See if this helps...

Your columns on sheet2 needed to trade places. I added the IFERROR to you VLOOKUP so you don't get a bunch of #N/As

[jewellove]

Dear Bebo and Steve, Both formula work... Thank so much..

[kvsrinivasamurthy]

You can use this in 3rd Row and drag down.
yOUR Sheet1 B column containsNumbers and Sheet2 Column C contains numbers in Text format.Both should be in same format.

Please Login or Register  to view this content.

[jewellove]

Yes. I copied the formula to the next cell.. I received NA because the cell in sheet 2 is in text.. I tried changing the text format cells to number by right click format cells then number. It didn't work.. What is the best way to do this..

[kvsrinivasamurthy]

Select the C column range.
Then Select DATA , Text to columns

In the dialogue box

Delimited , Next
Next
Column Data Format , General , Finish


You will find all are converted to number format.

[benishiryo]

hi jewellove. i think your stock code is meant to be a text, cause it's supposed to have codes with preceding 0s or even alphanumeric. so question should be why Sheet1 column B is in numbers.

but if your data is indeed like this and you want to convert Sheet2 to numbers, an alternative to kvsrinivasamurthy's method is to select Sheet2!C2:C943:
scroll back to the top. you should see an exclamation mark in C2. click on it & Convert to number

[kvsrinivasamurthy]

If the stock code contains preceding 0's ,it is better to convert Sheet1 Column B into TEXT format.
Select the required range ,Right click ,Format cells
In Number Tab select Text and OK.

[jewellove]

I have two sheets with different format because the data was taken from different sources.. Okay I got it. I checked on
kvsrinivasamurthy's "Text to Column" and it changes all cells from number to text and vice versa.. Great Tip!!!

Benishiryo is right that it should be text because some stock codes is proceeded by zeros. I will change all codes to text. Very good advice, guys... Thank you...

[jewellove]

I tried Benishiryo's tip to convert cell to text or number... It's a shortcut.. hahahahaha