VLookup #No Match Error

Cinda.q

I am looking for guidance for the utilization of the VLookup formula. I have formatted this formula within one of my Smartsheet sheets and I keep getting the #No Match error message, I have verified this information already:

1) Range to retrieve data from is 3 columns wide by about 1000 lines long, so I don't believe that I have hit the capacity 

2) Confirmed with co-workers the set-up of my formula (=vlookup(the cell within my sheet that I need the data to reference, the additional sheet range to find the data, then the column # to retrieve the data from, "false")

3) Tested the formula with values that are in the underlying sheet and the information is present

Looking for any advice at the moment!

 

Paul Newcome

Can you maybe provide some screenshots and copy/paste the actual formula you are using?

Cinda.q

Screenshots are attached

Paul Newcome

Are the 2 columns in both sheets formatted the same?

Cinda.q

All columns have the exact same heading and are formatted the same. The screenshot underneath the formula screenshot is the sheet that I want the data to pull into and bottom screenshot is the data source so you can see that they are titled the same and both are free text fields.  Any other thoughts?

Paul Newcome

Try this...

 

=VLOOKUP(VALUE([Project/ Cost Center]1), {Range}, 2, false)

 

Also... Are any of the values within the VLOOKUP function populated by a formula?

Cinda.q

Tried adding value to the formula and still the same result. None of the values in the reference sheet are formulas (I think that is what you are asking) and there are no formulas used to enter the project/ cost center in the sheet with the vlookup formula. The idea I was hoping for is to enter the project/cost center and then that text would trigger the lookup of the associated company code. 

I appreciate the assistance and if you have any other thoughts I am willing to try them out. 

 

Paul Newcome

The only other thing I can think of is verifying your range reference covers the correct columns. Other than that I would suggest contacting Smartsheet directly.

jvf-sap

I'm having same issue. Were you able to connect with smartsheet team to resolve this?

Sophie D

I have the same issue. I've sent an email to the Support team a week ago and still not answer...

 

Sophie D

From my customer success manager

The easiest way to get these values converted will be with a formula and a "helper" column in your sheet. I recommend adding an extra column in your sheet (it will be deleted later), then doing the following:

• In your new column, enter the formula =VALUE(your_number_column@row)
• Drag-fill or copy this formula through all the rows in your sheet in your new column
• Select all the cells in your new column and Copy them
• Use right-click & Paste Special to paste only values into your original column with the numbers appearing in text format
• Your numbers should appear correctly at this point

It works for me!

Hope it will work for you too

Paul Newcome

This just seems like an unnecessary workaround for something that shouldn't be "broken" in the first place. I have tested the VLOOKUP again without using the above "fix", and everything worked fine. I had numbers populating based on numbers using x-sheet references, and did not have to bother with the helper column and copy/paste.

EEGordon

I'm having this issue now. I have one range populating 4 VLOOKUP formula columns using one reference column but then when I try to use a different range with the same reference column I'm getting a #NOMATCH error. I've verified all column titles, formatting, and formulas are correct. I would rather not have to do the copy paste work around...

ON

I had this issue too, worked fine for some records and not for others - resolved it by not using the primary column on the resource sheet. Vlookup worked fine as soon as I moved everything in the ref sheet along one column and adjusted the formula in the master sheet to match... no idea why that worked but it did.

Remz

I had the same issue and resolved it by using Primary column and removing a formula driven column! Thanks everyone for the insights!

Nasir@EBC

I think my issue is even worse!

The vlookup function is pulling data from all the rows EXCEPT rows 244 to 266!

The format of all the columns is identical, and I just can't figure out why it's happening like this.

Any tips please?