Non matching text data of the same values

[Mike]

A text cell value = 12345678 with a green triangle in the left upper corner
of the cell will not match a text cell value = 12345678 with no green
triangle when doing a VLOOKUP.
When is the green triangle inserted and how can it be managed.

 

[Thomas [PBD]]

The green triangle is probably an error/alert message. With this example,
probably stating the the data entered into the field is entered as Text. One
way to get around this is to add a VALUE(<cell>) function to the cell which
you are attempting to lookup as a non-text. The VALUE function will force
the cell to be pulled as a Number.

 

[Mike]

Thanks for your help.
Your suggestion provides a good workaround to my issue.

The downside of this technique is that lead zeros are suppressed/lost as the
VALUE function converts from Text to General.

I use the text data format to retain the lead zeros.

I would like to understand when the green triangle in the left upper corner
is set. If I have a column of text data with some cells with the green
triangle and other cells with no green triangle, how can I either have all
cells with green triangles or all cells without the green triangles

The green triangle is probably an error/alert message. With this example,
probably stating the the data entered into the field is entered as Text. One
way to get around this is to add a VALUE(<cell>) function to the cell which
you are attempting to lookup as a non-text. The VALUE function will force
the cell to be pulled as a Number.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

A text cell value = 12345678 with a green triangle in the left upper corner
of the cell will not match a text cell value = 12345678 with no green
triangle when doing a VLOOKUP.
When is the green triangle inserted and how can it be managed.

 

[Thomas [PBD]]

Highlight all of the cells with the green arrow. If you right click (or just
single click) on the error code, it will ask you what you want to do with it.
You can tell it to ignore the error.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

Thanks for your help.
Your suggestion provides a good workaround to my issue.

The downside of this technique is that lead zeros are suppressed/lost as the
VALUE function converts from Text to General.

I use the text data format to retain the lead zeros.

I would like to understand when the green triangle in the left upper corner
is set. If I have a column of text data with some cells with the green
triangle and other cells with no green triangle, how can I either have all
cells with green triangles or all cells without the green triangles

The green triangle is probably an error/alert message. With this example,
probably stating the the data entered into the field is entered as Text. One
way to get around this is to add a VALUE(<cell>) function to the cell which
you are attempting to lookup as a non-text. The VALUE function will force
the cell to be pulled as a Number.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

A text cell value = 12345678 with a green triangle in the left upper corner
of the cell will not match a text cell value = 12345678 with no green
triangle when doing a VLOOKUP.
When is the green triangle inserted and how can it be managed.