#NO MATCH Error when using VLOOKUP to look up the text displayed on a hyperlink

John Thrasher

Hi!

I'm trying to use vlookup to return data (a version number) using match data (document numbers). I have the VLOOKUP formula as =VLOOKUP([Document Number]1, {OP - Smartsheet Master Document List Range 2}, 7, false)

where

[Document Number]1 is a text/number column on the sheet I am working in

{OP - Smartsheet Master Document List Range 2} is a link to another smartsheet, where the document numbers are - but these cells contain a hyperlink

I'm getting a no match error with this set up. Any advice or alternative solutions?

Thank you

Paul Newcome

Are you able to provide screenshots with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

John Thrasher

Sure - here's a few screenshots of the first sheet, the formula, and the referenced sheet within the formula

Paul Newcome

How is the document number entered on the target sheet?

John Thrasher

Both document numbers cells are entered manually

Paul Newcome

I want to try something...

In your reference sheet, insert a text/number column and enter

=VALUE([QAX No.]@row)

If you do not get an error, reference that column in your cross sheet reference. What happens then?

John Thrasher

Hey, that worked! Any thoughts on a formula that would work for both numbers and text? A few of the data entries are letters/numbers, instead of straight numbers

Paul Newcome

Ok. How about this...

Add a text/number column on the target sheet.

Enter this:

=[Document Number]@row + ""

Reference this new column in your VLOOKUP.

=VLOOKUP([Document Number Helper]@row, {OP - Smartsheet Master Document List Range 2}, 7, false)

Change your cross sheet reference to point to the original hyperlink column and then you can delete the helper column on your Reference sheet.

Using the + "" converts everything to a text string. The initial problem was that when you manually enter a string of numbers it is a numerical value whereas the Hyperlink column is text values. Initially we converted the hyperlinks to numbers, but since you can also have text in them as well, we switched it to convert the data in the target sheet to text.

jonnyp

I'm having the same issue.

When doing a lookup to the table, half the values are numerical only while the other half are text only. Using my original VLOOKUP, I got matches on all the numerical values (these are the first to occur in the list) but encountered a #NO MATCH for the text values.

I created a helper column as described above and pointed the VLOOKUP to span the original lookup column, helper column, and return column and bumped out the 'column_num' to return the corresponding column. The problem now is that I get even fewer matches on the numerical values and still no match on the text values.

Any ideas?