Case sensitive formula

Connie Cochran
Connie Cochran ✭✭✭✭
edited 02/02/24 in Formulas and Functions

I have a formula that is check to see if the event IDs are the same and if yes then return the total registration. The problem is that some of the event ID's look the same but are different due to case sensitivity. Is there a way to ensure that it is also checking the case?

current formula =IFERROR(VLOOKUP([Event ID]257, {Appointment Report Range 1}, 9, false), "")

example of event ids - a06Vn000001dFeX and a06Vn000001dFex

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Connie Cochran

    I believe MATCH and VLOOKUP are both case insensitive (as is CONTAINS), but FIND is case sensitive.

    Could you do something with FIND? This could work if the thing you are matching on is static, which it appears to be (row 257 in Event ID column).🤞 You could add a helper column to the Appointment Report with a formula to FIND where there is a match between the ID in that sheet and the one you are looking for.

    =FIND("Value from EventID row 257", [Appointment ID to match]@row) = 1

    Then you can do a LOOKUP or MATCH to return column 9 from the appointment sheet where the helper column =1

  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/02/24

    Deleted this as it double posted - FAO Board people - this is the error I saw when it double posted.

    What happened?
    The initial connection between Cloudflare's network and Vanilla timed out. As a result, the web page can not be displayed.
    What can I do to resolve this?
    Please try again in a few minutes. If the problem persists, you can contact the site owner to let them know you were unable to reach the website.
    

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!