VLOOKUP on column with letters and numbers results in #NOMATCH

jonnyp

I have a simple VLOOKUP to pull back the related column from a 2 column table. It consists of either letters only (WORD) or numbers only (123) for each entry but entries span both types. When the function is in place I receive a #NOMATCH error for all the letters only entries. I've tried adding a helper column that refers to the initial column (=VALUE(column_name@row + "") as well as =column@row) but neither seem to do the trick. Looking through existing entries seems hit or miss for success. Any news on this front?

Paul Newcome

I honestly don't like working with VLOOKUP. Let's try switching over to an INDEX/MATCH.

=INDEX({Column To Pull From}, MATCH([Docs Helper]@row, {Source Sheet Doc Helper}, 0))

Paul Newcome

The first cross sheet reference should be the direction column only, and the second cross sheet reference should be the doc helper column only.

Paul Newcome

You need to add that column to BOTH sheets and ditch the VALUE function.

=[Column Name]@row + ""

This converts everything into text values. Doing this on both sheets and then referencing these two columns should clear things up.

jonnyp

Hi @Paul Newcome. The current formula is below. As you can see it includes a cross reference to another sheet. Are you saying that I'll need a helper column in both the source and the destination sheets? i.e. a helper for the "Docs" column in the destination sheet and a helper for the lookup column in the {Global Doc Type} cross reference source sheet? Not fully understanding what you're saying. Appreciate the guidance!

=VLOOKUP(Docs@row, {Global Doc Type}, 2)

Paul Newcome

Yes. Whatever column you are matching on. You will need a helper column in both sheets that converts the entry in every row into a text value so that all data types are the same.

jonnyp

Still running into a #NO MATCH on everything now. Maybe you can help out. Here's what I've done.

For the destination sheet, I've created a helper column for "Docs" called "Docs Helper"

=Docs@row + ""

For the source sheet, I've created a helper column for "Document" called "Document Helper"

=Document@row + ""

I've created a column formula on the destination sheet

=VLOOKUP([Docs Helper]@row, {Global Doc Type}, 2)

where {Global Doc Type} is a cross reference sheet that contains two columns: "Document Helper" and the column to match to.

Paul Newcome

I honestly don't like working with VLOOKUP. Let's try switching over to an INDEX/MATCH.

=INDEX({Column To Pull From}, MATCH([Docs Helper]@row, {Source Sheet Doc Helper}, 0))

jonnyp

I think we're close! I've updated the formula in the "Direction" column of my destination sheet but I'm getting spotty results. Not sure why.

Here's what I have.

=INDEX({Direction}, MATCH([Docs Helper]@row, {Global Doc Type}, 0))

Where...

{Direction} is the cross reference column that contains the direction (inbound or outbound) in the source sheet.

[Docs Helper] is the helper column for [Docs] in the destination sheet.

{Global Doc Type} is the cross reference lookup that contains two columns: [Document Helper] and the column to match to, [Direction] in the source sheet.

Attaching some screenshots as well to help illustrate.

Destination Sheet with formula 'working' mostly for numerical entries

Destination sheet again with formula not working for text entries.

Source sheet with lookup info

Paul Newcome

The first cross sheet reference should be the direction column only, and the second cross sheet reference should be the doc helper column only.

jonnyp

Yes! This did it. This was driving me crazy. Thank you.

Paul Newcome

Happy to help. 👍️