+ Reply to Thread
Results 1 to 4 of 4

Using EXACT function without case-sensitivity?

Hybrid View

  1. #1
    Registered User
    Join Date
    04-09-2006
    Location
    London
    Posts
    70

    Using EXACT function without case-sensitivity?

    Guys,

    I have the below formula which is meant to look for particular numbers and if Excel finds those numbers, then to compare the cell (which has text in it) in the next column with a cell in a range. If it finds a match, then it will get the value in the range, otherwise it will say "Not found".

    Unfortunately, due to a EXCEL getting confused between many similar worded cells, I have to use EXACT function, but as EXACT is case sensitive, its causing other problems. Is there a way to use Exact withOUT case sensitivity or perhaps a similar EXCEL function?

    Can anyone recommend what I shoud do please.

    Formula: =IF(ISERROR(IF(AND(OR($A33=1111,$A33=1112,$A33=1113),EXACT(VLOOKUP($B33,$K$3:$K$46,1,FALSE),$B33)),VLOOKUP($B33,$K$3:$L$46,2,FALSE),IF(AND(OR($A33=2221),EXACT(VLOOKUP($B33,$K$48:$K$71,1,FALSE),$B33)),VLOOKUP($B33,$K$48:$L$71,2,FALSE),"Core"))),"Core",

    P.S: because of the multiple IFs, Im only pasting the first half of the iserror argument. (otherwise the formula would be too long...)

    P.P.S: Im using Excel 2007.

    Thanks & Regards,
    Last edited by Kayote; 04-23-2007 at 07:16 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,721
    Instead of

    EXACT(VLOOKUP($B33,$K$3:$K$46,1,FALSE),$B33)

    just use

    VLOOKUP($B33,$K$3:$K$46,1,FALSE)=$B33

  3. #3
    Registered User
    Join Date
    04-09-2006
    Location
    London
    Posts
    70
    Daddylonglegs,

    Thanks yet again but for some odd reason, I donot get the desired results with using simple =A1.

    The words that excel is comparing are like: (e.g) Alan Johnston, Nick Johnston, Heidi Johnston. Because of the repeating Johnston, I am having to use EXACT function.

    Regards,

  4. #4
    Registered User
    Join Date
    04-09-2006
    Location
    London
    Posts
    70
    Ok I created the miniature model similar to my spreadsheet & the same formula is working fine in it while it is not working properly in my original spreadsheet.

    Maybe its a bug in excel or something...

    Regards,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1