VLOOKUP Doesn't Appear To Work With Auto-Number Columns/Cells

Randy Willey

I have a main sheet containing a list of projects with the first column containing the ID of the project which is configured as an auto-number column. I am working on setting up another sheet to track projects tasks and want to be able to link a task to its associated project in the project sheet. There are select columns in the project sheet that I wish to display in the tasks sheet and am attempting to use VLOOKUP. However, when I establish the formula I consistently get "NO MATCH" returned. To confirm that I was entering the formula correctly I used a different sheet where the ID column was not auto-numbered but simply a text/number with manual entry. The formula returned the expected result in that instance, so it appears that the VLOOKUP doesn't work when the lookup column is auto-numbered. Is there another way to accomplish my goal?

Andrée Starå

Hi Randy,

I use this method all the time in my client solutions, so it should work.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Martin van Gogh

I have a similar issue. The VLOOKUP is returning a value from the wrong row. To test this I changed the value to be returned from column 8 to column 1 and sure enough it doesn't return the proper value. In this exampe the AutoNum column has a value of I327, and when the VLOOKUP returns column 1 it returns I3 rather than I327. For most of the values in the AutoNum column I get #NO MATCH even though there are matching values.

=VLOOKUP([Column_ID]@row, {SheetName Range 2}, 1

[Column_ID] = I327 which has a match in SheetName

Range 2 includes 8 columns, I want the value from column 8, I just changed it to 1 to see what it would return as it was providing the wrong value.

The screen shot is of the AutoNum column configuration in SheetName.

Capture.JPG

Martin van Gogh

I did more testing, I created two simple sheets to test and it works properly. More testing on the original sheet shows that it is only reading the first two characters of the SheetName, AutoNum column. Any ideas what could cause that?

Andrée Starå

Hi @Martin van Gogh

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or more detailed screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Frenk

Hi

I have the same problem as described in initial description.

Any idea

Frank