Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

Your key column in the lookup table is numeric, but the value that you're
matching up is Text.

You can coerce those Text values to number values by:
selecting an empty cell
edit|copy
select the range of text numbers
edit|Paste special|check add



DTTODGG wrote:

Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

--

Dave Peterson

Thank you for your quick response, Dave!

You are correct, the key column is numeric and the matching column is text.

The problem is, the matching column is text because it is a 13 digit number
:-( Therefore, I can't use your suggestion.

So, would I rather coerce the key column to have the little apostrophe? I
don't know how to do this. It seems so simple. I can manually change the key
column to have an apostrophe and then everything works. If I convert it to
text it does not.

The question is: what's the difference between the little green triangle and
formating a cell to "text"? And how can you get little green apostophes in
many rows?

VLOOKUP
B2 (where B2 has the little green triangle (because the numbers range from 5
to 13 digits)
filename and valid data (where col A is a 5 digit number and col B is a 13
digit number with a little green triangle)
1020600060054 (where this number has the little green triangle)
False

Thank you again.

"Dave Peterson" wrote:

Your key column in the lookup table is numeric, but the value that you're
matching up is Text.

You can coerce those Text values to number values by:
selecting an empty cell
edit|copy
select the range of text numbers
edit|Paste special|check add



DTTODGG wrote:

Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

--

Dave Peterson

I don't understand why 13 digits would represent a problem.

And if =vlookup(value(... worked, then I think that the other suggestion would,
too.

DTTODGG wrote:

Thank you for your quick response, Dave!

You are correct, the key column is numeric and the matching column is text.

The problem is, the matching column is text because it is a 13 digit number
:-( Therefore, I can't use your suggestion.

So, would I rather coerce the key column to have the little apostrophe? I
don't know how to do this. It seems so simple. I can manually change the key
column to have an apostrophe and then everything works. If I convert it to
text it does not.

The question is: what's the difference between the little green triangle and
formating a cell to "text"? And how can you get little green apostophes in
many rows?

VLOOKUP
B2 (where B2 has the little green triangle (because the numbers range from 5
to 13 digits)
filename and valid data (where col A is a 5 digit number and col B is a 13
digit number with a little green triangle)
1020600060054 (where this number has the little green triangle)
False

Thank you again.

"Dave Peterson" wrote:

Your key column in the lookup table is numeric, but the value that you're
matching up is Text.

You can coerce those Text values to number values by:
selecting an empty cell
edit|copy
select the range of text numbers
edit|Paste special|check add



DTTODGG wrote:

Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

--

Dave Peterson


--

Dave Peterson

Alternately, if you continue to get imported data of this form, you could
embed the conversion from text to number in the lookup formula. Instead of
=vlookup(a1...), =vlookup(value(a1)...)

"Dave Peterson" wrote:

Your key column in the lookup table is numeric, but the value that you're
matching up is Text.

You can coerce those Text values to number values by:
selecting an empty cell
edit|copy
select the range of text numbers
edit|Paste special|check add



DTTODGG wrote:

Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

--

Dave Peterson

Brillant! So, simple, so beautiful!

I REALLY do appreciate EVERYONE who contributes on this site. It has been a
GREAT learning experience for me.

Thank-you EVERYONE.

"bpeltzer" wrote:

Alternately, if you continue to get imported data of this form, you could
embed the conversion from text to number in the lookup formula. Instead of
=vlookup(a1...), =vlookup(value(a1)...)

"Dave Peterson" wrote:

Your key column in the lookup table is numeric, but the value that you're
matching up is Text.

You can coerce those Text values to number values by:
selecting an empty cell
edit|copy
select the range of text numbers
edit|Paste special|check add



DTTODGG wrote:

Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

--

Dave Peterson