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!

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!

--
Biff
Microsoft Excel MVP


"Mike C" <js2k111@yahoo.com> wrote in message
news:e8718acd-9f2c-488a-afbc-e8ed90674e28@d1g2000hsg.googlegroups.com...
> 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!

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



On May 12, 12:45*pm, Mike C <js2k...@yahoo.com> wrote:
> 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!

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

On May 12, 5:45*pm, Mike C <js2k...@yahoo.com> wrote:
> 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!

On May 12, 12:09*pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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: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.

>
> What does your formula look like? Need more details!
>
> --
> Biff
> Microsoft Excel MVP
>
> "Mike C" <js2k...@yahoo.com> wrote in message
>
> news:e8718acd-9f2c-488a-afbc-e8ed90674e28@d1g2000hsg.googlegroups.com...
>
>
>
> > 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!- Hide quoted text -
>
> - Show quoted text -

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


"Mike C" <js2k111@yahoo.com> wrote in message
news:2eff9d72-9bc7-4928-a485-a8ae7b8a1192@m3g2000hsc.googlegroups.com...
On May 12, 12:09 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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: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.

>
> What does your formula look like? Need more details!
>
> --
> Biff
> Microsoft Excel MVP
>
> "Mike C" <js2k...@yahoo.com> wrote in message
>
> news:e8718acd-9f2c-488a-afbc-e8ed90674e28@d1g2000hsg.googlegroups.com...
>
>
>
> > 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!- Hide quoted text -
>
> - Show quoted text -

On May 12, 9:20*pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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
>
> "Mike C" <js2k...@yahoo.com> wrote in message
>
> news:2eff9d72-9bc7-4928-a485-a8ae7b8a1192@m3g2000hsc.googlegroups.com...
> On May 12, 12:09 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
> > 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: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.
>
>
>
>
>
> > What does your formula look like? Need more details!
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "Mike C" <js2k...@yahoo.com> wrote in message
>
> >news:e8718acd-9f2c-488a-afbc-e8ed90674e28@d1g2000hsg.googlegroups.com...
>
> > > 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!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Thanks Biff. Pete's suggestion did the trick

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

Pete

On May 13, 5:28*am, Mike C <js2k...@yahoo.com> wrote:
>
> Thanks Biff. Pete's suggestion did the trick