Forcing a cell to be formatted as text - when using the Match Indexfunctions

[Mike C]

Hello - I am trying to do use the Match and Index functions to perform
a Vlookup. However, the column for which I am attempting to match
values will not convert to text format (thus disallowing me from doing
the Lookup).

Does anyone have any suggestions.

FYI, the way that I know that it isn't text is when I use the
=istext() function, i get "false".

And I know that my formula is correct, because when i actually paste
the cells from the other table, the Formula produces the desired
result.

Thanks for any help!

 

[T. Valko]

Not sure what you mean.

Do you mean the lookup_value is TEXT but the lookup_array is not?

What does your formula look like? Need more details!

 

[Imonit]

As Bif mentioned earlier, some more information would be helpful but
something I noticed from this text is;

And I know that my formula is correct, because when i actually paste
the cells from the other table, the Formula produces the desired
result.

The possibility that this above test example does work is because the
formats were pasted with the information from the other table.
Thought I'd mention that as it might help you to troubleshoot the
issue your having.

Hope that helps!

-Imonit

 

[Pete_UK]

I think that you mean that the lookup value is a number but the values
in the lookup table is text (or vice versa). If that is the case then
you could use this approach:

=INDEX(list_2,MATCH(A1&"",list_1,0))

Here A1 (a proper number) has "" joined on to it, effectively making
it into text to match the format of the values in list_1.

If your data is the other way around then you might have something
like this:

=INDEX(list_2,MATCH(A1*1,list_1,0))

Here A1 (a "text" number) is multiplied by 1 to turn it into a proper
number.

It's always helpful if you post the formula that you have tried with,
so we have some idea of cell references etc., but hopefully you can
adapt this.

Hope this helps.

Pete

 

[Mike C]

Not sure what you mean.

Do you mean the lookup_value is TEXT but the lookup_array is not?

In Sheet1!c2:c200, I have social security numbers and in
Sheet1d1:d200, I have the corresponding names.

In Sheet 2!e1:e500 I have another set of social security numbers.

I am trying to discover whether the socials in sheet 2 have a match
from sheet1 (i.e., my lookup formula is written in sheet2, and is
comparing to the socials in sheet1.

So, my formula in Sheet2 is =INDEX('sheet1!D,MATCH('Sheet2!
E2,'Sheet1!C:C,FALSE))

When I physically paste the social from Sheet 1 into sheet 2 (i.e., in
cell e2), the formula works. But the problem is that it won't work
otherwise (b/c for some reason the format of the cells in sheet 2,
column E cannot be changed to text, I believe).

Thanks for any suggestions. Hope this doesn't confuse you further.

 

[T. Valko]

Take a look at Pete's suggestion.

Also see this for common problems with lookups:

http://contextures.com/xlFunctions02.html#Trouble

--
Biff
Microsoft Excel MVP

Not sure what you mean.

Do you mean the lookup_value is TEXT but the lookup_array is not?

In Sheet1!c2:c200, I have social security numbers and in
Sheet1d1:d200, I have the corresponding names.

In Sheet 2!e1:e500 I have another set of social security numbers.

I am trying to discover whether the socials in sheet 2 have a match
from sheet1 (i.e., my lookup formula is written in sheet2, and is
comparing to the socials in sheet1.

So, my formula in Sheet2 is =INDEX('sheet1!D,MATCH('Sheet2!
E2,'Sheet1!C:C,FALSE))

When I physically paste the social from Sheet 1 into sheet 2 (i.e., in
cell e2), the formula works. But the problem is that it won't work
otherwise (b/c for some reason the format of the cells in sheet 2,
column E cannot be changed to text, I believe).

Thanks for any suggestions. Hope this doesn't confuse you further.

 

[Mike C]

Take a look at Pete's suggestion.

Also see this for common problems with lookups:

http://contextures.com/xlFunctions02.html#Trouble

--
Biff
Microsoft Excel MVP





In Sheet1!c2:c200, I have social security numbers and in
Sheet1d1:d200, I have the corresponding names.

In Sheet 2!e1:e500 I have another set of social security numbers.

I am trying to discover whether the socials in sheet 2 have a match
from sheet1 (i.e., my lookup formula is written in sheet2, and is
comparing to the socials in sheet1.

So, my formula in Sheet2 is =INDEX('sheet1!D,MATCH('Sheet2!
E2,'Sheet1!C:C,FALSE))

When I physically paste the social from Sheet 1 into sheet 2 (i.e., in
cell e2), the formula works.  But the problem is that it won't work
otherwise (b/c for some reason the format of the cells in sheet 2,
column E cannot be changed to text, I believe).

Thanks for any suggestions. Hope this doesn't confuse you further.







- Show quoted text -

Thanks Biff. Pete's suggestion did the trick

 

[Pete_UK]

Thanks for the feedback, and for the email to confirm the approach
worked.

Pete