 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Forcing a cell to be formatted as text - when using the Match Index functions
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!  SignatureBiff Microsoft Excel MVP > 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 [quoted text clipped - 11 lines] > > Thanks for any help! > 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! > [quoted text clipped - 19 lines] > > - Show quoted text - Take a look at Pete's suggestion. Also see this for common problems with lookups: http://contextures.com/xlFunctions02.html#Trouble SignatureBiff Microsoft Excel MVP 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! > [quoted text clipped - 23 lines] > > - Show quoted text - > Take a look at Pete's suggestion. > [quoted text clipped - 60 lines] > > - 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 > Thanks Biff. Pete's suggestion did the trick 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 > 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 [quoted text clipped - 11 lines] > > 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 > 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 [quoted text clipped - 11 lines] > > Thanks for any help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.