TEXT = text

I have some data in an Excel table which uses case sensitive codes. Does 
anyone know of a way to perform case sensitive VLookups.

Example.

A1 contains "TEXT", A2 contains "One".
B1 contains "text", B2 contains "Two"

If I enter "text" in cell A4 and the following VLookup in B4:

=VLOOKUP(A4;A1:B2;2;FALSE)

I get the answer "One", but I would like to see "Two".

I have found a work around by converting a number of characters to their 
ASCII codes, but this is very messy.

I know that the FIND function is case sensitive whereas the SEARCH function 
is not, is there a way to force a case sensitive VLookup?

Thanks for your thoughts.

Thomas 


0
9/19/2009 6:28:18 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1895 Views

Similar Articles

[PageSpeed] 47

Try this array formula** :

=INDEX(B1:B2,MATCH(TRUE,EXACT(A1:A2,A4),0))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

In this case EXACT means just that!

-- 
Biff
Microsoft Excel MVP


"Thomas Hardy" <thomas_hardyDELETE@excite.com> wrote in message 
news:h937rs$i5r$02$1@news.t-online.com...
>I have some data in an Excel table which uses case sensitive codes. Does 
>anyone know of a way to perform case sensitive VLookups.
>
> Example.
>
> A1 contains "TEXT", A2 contains "One".
> B1 contains "text", B2 contains "Two"
>
> If I enter "text" in cell A4 and the following VLookup in B4:
>
> =VLOOKUP(A4;A1:B2;2;FALSE)
>
> I get the answer "One", but I would like to see "Two".
>
> I have found a work around by converting a number of characters to their 
> ASCII codes, but this is very messy.
>
> I know that the FIND function is case sensitive whereas the SEARCH 
> function is not, is there a way to force a case sensitive VLookup?
>
> Thanks for your thoughts.
>
> Thomas
> 


0
biffinpitt (3172)
9/19/2009 7:06:32 PM
Biff,

That's excellent, thank you very much!

Thomas

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:OeVCPxVOKHA.4700@TK2MSFTNGP05.phx.gbl...
> Try this array formula** :
>
> =INDEX(B1:B2,MATCH(TRUE,EXACT(A1:A2,A4),0))
>
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> SHIFT key then hit ENTER.
>
> In this case EXACT means just that!
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Thomas Hardy" <thomas_hardyDELETE@excite.com> wrote in message 
> news:h937rs$i5r$02$1@news.t-online.com...
>>I have some data in an Excel table which uses case sensitive codes. Does 
>>anyone know of a way to perform case sensitive VLookups.
>>
>> Example.
>>
>> A1 contains "TEXT", A2 contains "One".
>> B1 contains "text", B2 contains "Two"
>>
>> If I enter "text" in cell A4 and the following VLookup in B4:
>>
>> =VLOOKUP(A4;A1:B2;2;FALSE)
>>
>> I get the answer "One", but I would like to see "Two".
>>
>> I have found a work around by converting a number of characters to their 
>> ASCII codes, but this is very messy.
>>
>> I know that the FIND function is case sensitive whereas the SEARCH 
>> function is not, is there a way to force a case sensitive VLookup?
>>
>> Thanks for your thoughts.
>>
>> Thomas
>>
>
> 


0
9/22/2009 8:07:04 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Thomas Hardy" <thomas_hardyDELETE@excite.com> wrote in message 
news:h9bap6$447$01$1@news.t-online.com...
> Biff,
>
> That's excellent, thank you very much!
>
> Thomas
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:OeVCPxVOKHA.4700@TK2MSFTNGP05.phx.gbl...
>> Try this array formula** :
>>
>> =INDEX(B1:B2,MATCH(TRUE,EXACT(A1:A2,A4),0))
>>
>> ** array formulas need to be entered using the key combination of 
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
>> SHIFT key then hit ENTER.
>>
>> In this case EXACT means just that!
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Thomas Hardy" <thomas_hardyDELETE@excite.com> wrote in message 
>> news:h937rs$i5r$02$1@news.t-online.com...
>>>I have some data in an Excel table which uses case sensitive codes. Does 
>>>anyone know of a way to perform case sensitive VLookups.
>>>
>>> Example.
>>>
>>> A1 contains "TEXT", A2 contains "One".
>>> B1 contains "text", B2 contains "Two"
>>>
>>> If I enter "text" in cell A4 and the following VLookup in B4:
>>>
>>> =VLOOKUP(A4;A1:B2;2;FALSE)
>>>
>>> I get the answer "One", but I would like to see "Two".
>>>
>>> I have found a work around by converting a number of characters to their 
>>> ASCII codes, but this is very messy.
>>>
>>> I know that the FIND function is case sensitive whereas the SEARCH 
>>> function is not, is there a way to force a case sensitive VLookup?
>>>
>>> Thanks for your thoughts.
>>>
>>> Thomas
>>>
>>
>>
>
> 


0
biffinpitt (3172)
9/22/2009 10:00:23 PM
Reply:

Similar Artilces:

How to keep fonty style to write in a text with different style?
Hello everyone, I have started giving lessons. I need to add my corrections to the text my pupil send me by e-mail. Right now I have to change the font style each and every time I have to write something in the middle of her text. As it´s a correction, I type in red bold letters while the original text is in normal black font. Is there any option I can use so that I don´t have to change the font every time? Any option that sets the type font independently of the style of the original text? Thank you. Hi Cristina, Word has a Track Changes feature that change ...

UDF to Convert formula results to text
Can a UDF be used to convert formula results from column A to text in column B? I know that I can manually cut and paste "special" to achieve this, but I want to avoid a manual step. Instead of a UDF use a macro. Change columns(2) to columns(1) to change the column withOUT the need for an additional column. Sub converttovalues() Columns(2).Value = Columns(1).Value End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Don" <don87109@hotmail.com> wrote in message news:%230kPQ8gDKHA.4608@TK2MSFTNGP02.phx.gbl... > Can a UD...

Use existing jpeg as background and place text on top
I want to use an existing jpg file as the background on a page and place text on top of t. How do I do that? What have you tried so far? -- JoAnn Paules MVP Microsoft [Publisher] "florida" <florida@discussions.microsoft.com> wrote in message news:046328DC-F4A4-4E8D-B724-54C92371891A@microsoft.com... >I want to use an existing jpg file as the background on a page and place >text > on top of t. How do I do that? > I tried placing the jpg in background, but instead of getting one large image, I got a whole page of small images. florida "JoAnn Pa...

Macro to delete a text box and copy in new one
I need to set-up a macro to go from one file, file1, into another file, file2, and delete a text box in file2. Then, I need the macro to go back into file1 and copy a blank text box over to file2 and put it in the same location. Is this possible? Any information is appreciated. Thanks You could do it with something like: Option Explicit Sub testme() Dim fWks As Worksheet Dim tWks As Worksheet Dim fShape As Shape Dim tShape As Shape Dim tLocation As Range Set fWks = Workbooks("book1.xls").Worksheets("sheet1") Set tWks = Workbooks(...

Auto Populate text box based on combo box
Hello, I have a combobox named "Bulk" and a text box named "Routing", I would like the Routing box to auto populate after the user selects an option from "Bulk". The Bulk combo box is bound to a table named "Products and bulks". The Routing selection would be coming from the same table. Could someone please explain this to me? I have tried different codes like: Private Sub cboPersonID_AfterUpdate() Me.txtPersonName.Value = Me.cboPersonID.Column(1) End Sub Everytime I put this in the Combo Box's afterupdate property, an error message com...

Excel to Excel, Cut & Paste, 1 cell, text, multiplies file size f.
Hello I have two Excel files, the main file is ~1MB and the other is 43KB. When I Cut and Paste between the two the system takes >2 minutes to complete the C&P operation. After several C&P the delay is in the 10+ min range. If I save and exit the file the master is now over 10MB. Each operation is no more then 1 or 2 cells at a time, the cells are text only. If I cut from the cell but enter on the formula bar, 1 cell at a time, I have no problem. I do this operation all the time and have never had this problem before. I am assuming it is from the smaller Excel file but cannot...

Is it possible to Unicode only for menu text...
Hi i am working on application where i need menu text in Arabic. I have done it (VC++ 6)with the following method: I have added... Project-Settings-C++-Preprocessor definitions : _UNICODE & Project-Settings-Link-Category-OutPut : wWinMainCRTStartup Then i have write arabic text in menu enteries, it works fine. But in my application i an using lot of other data types like CString etc. These don't work if i not convert them in unicode. What i want to ask "is there is any way to unicode only the menu resouce not whole the application". Means i don't want t...

Line up text
I have a column, Members, where sometimes there can be multiple names. I pull a query and then run a report. However, the data entered is all on one line but I want the report to have it appear as a list. For example, the data in the table appears as Jane Doe - Spring 07 and John Doe - Spring 08. How do I get it to appear as follows in my report: Jane Doe - Spring 07 John Doe - Spring 08 My Access skills are basic so the more detailed the better. Thank you in advance. If all have a dash where you want to align on then you can use a calculation. Field_Title: Left(Left([YourField], Inst...

Paste Special as Text
I have a small program that drops text from a Word table into Excel using Paste Special as Text. Although Paste Special as Text should insert the contents of the clipboard as text without any formatting this does not appear to be happening as it did with Excel 2000 . Excel 2003 splits the data when it meets a New Line or Carriage Return and drops anything after it down to row below. Any ideas what is causing this behaviour Thanks for your assistance Phil ...

putting text into pictures once in outlook
Hi I frequently want to send photos from outlook. Is thee an easy way of insertng text into pictures, tried text box, but it will not work. thanks tim "Tim" <adslk4at@tpg.com.au> wrote in message news:4241e306@dnews.tpgi.com.au... > Hi I frequently want to send photos from outlook. Is thee an easy way of > insertng text into pictures, tried text box, but it will not work. thanks > tim > Try a good image editing program. Outlook isn't one of those. ...

How does 'save as text' work?
What is the difference between save, save as, save as text? When I 'save', or 'save as' I get a PDF file icon, when I 'save as text' I get a 'notepad' icon. I can access the PDF file but there's nothing in the 'notepad' icon to access. Where are you seeing these options? -- Stefan Blom Microsoft Word MVP "Ruben" <Ruben@discussions.microsoft.com> wrote in message news:6434BFFE-D523-4E3A-ADB9-53249E77667A@microsoft.com... > What is the difference between save, save as, save as text? > When I 'save'...

Extracting Partial Text from Field
A couple of weeks ago, I posted to this group for help extracting the To and By data from this field into two separate fields I could query and report on cs - Assigned Issue To: MARILYN MONROE Assigned by: JOHN KENNEDY I was given this and it worked GREAT: To: Mid([action_description],InStr([action_description],"Assigned Issue To:")+25,(InStrRev([action_description],"Assigned by:")-1)-(InStr([action_description],"Assigned Issue To:")+19)) I now have an Assigned To field that contains MARILYN MONROE and an Assigned By that contains ROBERT KENNEDY. The prob...

Text field size
I want to import data into a new table in Access 2007. Under Access Options, then Object designers--I put 50 in as the Default text size. But anytime I import data and it is a text field it will automaticlt make it 255 no matter what i do. I am trying to limit the size of the Database since most of my data would never exceed 50. Thanks -- Tom The good news is that Access doesn't waste space in text field. Even if the field is sized to 255 characters, if you put 50 characters in it, that's all it uses. About the only downside to having it set at 255 is that a fo...

x-axis text wraping
I'm working with data in a column chart that expands over 36 periods.=20 Rather than have the x-axis list 1 thru 36 I want x-axis to display = text.=20 I already know how to get the text there and to adjust the x-axis=20 orientation, etc. However, the graph automatically wraps the text once=20 it gets so long.=20 I don't want the text to wrap. Is there anyway to have the chart not=20 wrap the text? Besides decreasing the font of the x-axis. Brett wrote: > I'm working with data in a column chart that expands over 36 periods. > Rather than have the x-axis list 1 thru 36 I wa...

Convert Excel to comma delimited text
I have a comma delimited text file with a .txt extension. I converte it into excel to make some changes to the file and am having problem converting it back to comma delimited with .txt extention. When I'm i excel and "save as" the only comma delimited option I see is .csv. I it possible to save comma delimited with .txt extention? Any guidanc is greatly appreciated -- deac ----------------------------------------------------------------------- deacs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=232 View this thread: http://www.excelforum.com...

copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes
i have a formula composed of 3 lines in one cell: =" IF c_MSISDN_NDC" & $E3 & " IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 1, c_oper_name);'); utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 2, c_oper_name);');" this produces a text string, e.g. as below: " IF c_MSISDN_NDC70 IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_t...

text disapearing in a text box
I put a text box on a chart. After typing it in (it was a long text) I saved the chart I could still see all the text. After I saved it, I created a PDF. It was still fine. After I closed excel. The next time I opened the chart part of the text was gone. I tried to resize the text box, but part of the text was still gone. When I oringinally save the chart I didn't get an error message about the size and the pdf after the save showed all of the text. Thanks ...

Text boxes
When I open a new text box it always has 2 columns in it. How can I change it to just open with 1 box with no columns Thelma darling, what oh what version of Publisher are you using??? -- I am using Publisher 2000 Thelma wrote: > I am using Publisher 2000 What about them?! Was there supposed to be a question in there somewhere?! Sarah, Did you bother to read the thread? Of course not - it shows! May 2004 bring you a tinge of humanity and caring, as well as tolerance and intelligence. You're seriously lacking in all four areas. Merry Christmas, Mike Epona wrote: > Th...

Removing shadow from text under icons
My question is related to Windows 7. I post here as I don't see any more specific newsgroup... I configured the desktop theme as Windows Classic with a white solid background. I see the text under the desktop icons as white shadowed. How can I configure the text under icons to be simply black, wihtout any shadow? I see the same problem even using other desktop background solid colors... What setting should I change? Regards Marius On Sun, 7 Feb 2010 19:44:07 +0100, "Marius - Roma" <mr@nospam.local> wrote: > My question is related to Windows 7. I...

Is there a way to hide text in a cell?
I'm trying to format a spreadsheet where some information (passwords) should only be viewed by certain people.... not for the general viewer. Is there a way to hide text in a cell? I have done it in the past but I can't seem to figure this out now. IT For a very casual and inexperienced user you may get away with something like this, but anything you do can be easily un-done by just about anyone who could access these news groups. You could format the text to white so's it wouldn't show in the cell. You can go to Format>Cell>Protection and check "hidden"...

entering text on merged cells
Can anyone help me with this question? I want to enter text on merged cells. The wrap text box is checked. When I enter the text it looks ok and like it should fit within the merged cells but when I go out of the merged cells it displays: ###############################. Thanks Try formatting the cell as General (instead of Text). David Pincus wrote: > > Can anyone help me with this question? I want to enter > text on merged cells. The wrap text box is checked. > When I enter the text it looks ok and like it should fit > within the merged cells but when I go out of ...

text change
I'll try this again as I accidentally hit the enter key after typing the subject. In a spreadsheet I have all of my staff listed by their abbreviated names used to identify them on our server. I want to be able to convert there abbreviated names like "gcarls" to "Carlson, Glen". I think one of the If formualtions will work but I'm having difficulty. Can anyone please tell me how to do this? -- John See this: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "John" <John@discussions.microsoft.com> wr...

finding contrls text given dialog id and control id
Hi I want to find a control's text without creating the dialog. Is there any way ? thanks & regards ~ Rudresh "Rudresh" <Rudresh@discussions.microsoft.com> wrote in message news:C81B7D0F-3DAC-4E51-8039-038E45589672@microsoft.com... > Hi > I want to find a control's text without creating the dialog. > Is there any way ? The only way that springs to mind is to LoadResource the DLGTEMPLATE[EX], parse it and walk the subsequent DLGITEMTEMPLAT[EX] elements looking for an id match and then parse through it to find the title text if present. -- Jeff Partch [...

How do I stop Word putting text at bottom of page onto next page?
Word pushes some text at the bottom of the page onto the next page, thereby creating a gap at the bottom of the page. How do I get Word to stop doing this? The following settings may be relevant in such a case: "Keep with next," "Keep lines together," and "Widow/orphan control" (all found in the Paragraph dialog box). Note, however, that the best way to deal with this depends on what kind of a document you are creating. Is it a numbered outline of heading styles? A document with footnotes in it? -- Stefan Blom Microsoft Word MVP &quo...

Transparent text boxes
I have text boxes which have a fill color yet transparent center and then an image behind the text box. On the screen it shows up perfectly, yet when I print it the image is blocked. Any ideas? Thank You Publisher has issues with transparency. Create a rectangle the size of your text box, apply your transparency, right-click, save as picture, select the .png format, re-insert the saved image into your publication. -- Mary Sauer http://msauer.mvps.org/ "Sam@Postnet" <Sam@Postnet@discussions.microsoft.com> wrote in message news:2C9504A9-C26E-4025-8320-405ADE655DBC@mic...