Share via

Case sensitive vlookup or case sensitive if

Anonymous
2010-11-16T19:48:17+00:00

I am working on street name sign letter spacing template and I need to create a verticle lookup for case sentitive information.  It appears the the normal vlookup command is not case sentitive.  How can I determine if a charater is upper or lower case?  I can use that answer to drive my vlookup to the correct column in my spacing table.

I am using Excel 2002 XP Pro, but have acess to 2003 on XP Pro and 2007 on Vista Business 64 if it makes a difference.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2010-11-16T21:25:20+00:00

    A1 holds word Bernard. The formula =MID(A1,3,1) returns 'r' while =CODE(MID(A1,3,1)) returns 114 - ANSI code for the letter. best wishes

    Bernard Liengme, Nova Scotia, Canada http://people.stfx.ca/bliengme/

    Was this answer helpful?

    0 comments

Answer accepted by question author

  1. Anonymous
    2010-11-16T20:24:21+00:00

    I think I see what you are trying to do, but I get #N/A result.  In help I don't see that the EXACT command allows a range.  Is this a limitation of Excel 2002? 

    I got the array formula part to work with control+shift+enter. 

    Brad

    If you enetered it correctly and you get #N/A then there isn't a match.

    As a test set your lookup value to (say) an uppercase A. Now in the lookup range enter and uppercase A. Does it work then?

    If you need more help then post the orignal non case sensitive VLOOKUP and we'll convert it.

    If this post answers your question, please mark it as the Answer.

    Mike H

    Was this answer helpful?

    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-11-16T22:17:06+00:00

    I need to deal with each character individually.  I think I am on the right track now.  My next problem is what to do with code looking up a nul character.  I got around it with an if statement before the code command.  It may not be the cleanest way, but it works for now.

    Brad

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-11-16T21:34:02+00:00

    Thank you.  I spent quite awhile this afternoon reading Excel help, but your pointers gave me the direction I needed.  F1 is not much help if you don't know what to look for...

    Brad

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-11-16T21:07:02+00:00

    I am going to try to include your fornula in my worksheet.  On a related issue; how can I get the second, third, etc., character of the the word?  I need to parse out the letters to lookup the values independantly.  Before I was just having each letter entered into a cell by itself, but it would be nice if I could have Excel parse the words and only have one cell for data entry.

    Brad

    Was this answer helpful?

    0 comments