Forum Discussion

mjfox62's avatar
mjfox62
Copper Contributor
Mar 12, 2019

Lookup

I trying to use vlookup for the first time.

I have a workbook with 2 sheets.

Sheet 1 will have the vlookup for sheet 2.

I'm trying to input a customer number on sheet 1 to lookup the customer name on sheet 2 and place it on sheet 1.

Here is the formula I've entered: =VLOOKUP(C5,Sheet2!$A$2:$D$3100,2,FALSE)

I've copied this formula done about 25 cells on sheet 1.

Most if the cells have numbers that are being looked up and some are letters.  The ones with letters lookup fine.  The numbers are having problems.  I've tries to change the format for the cells to be General, Text, Numbers with no change.

 

The problem is that only the first 2 rows in the data sheet 2 will produce a right answer  The rest show N/A.

 

 

Any suggestions?

excel

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Mar 12, 2019
    Try changing the formula to: =VLOOKUP(C5&"",Sheet2!$A$2:$D$3100,2,FALSE)
    • mjfox62's avatar
      mjfox62
      Copper Contributor
      Mar 12, 2019

      Thanks.  That helped.

       

      I think there's something wrong with my data file format on the numbers.  Some of them will not display when entered and others still will. 

      Example:  the following data cells work - 1, 10

      The following data cells don't work - 100, 1000

      not sure if they are formatted funny.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Mar 13, 2019
        Part of your numbers are treated as text by Excel. Select all of them and choose Data, Text to columns and click Finish. Then use your original formula.