Results 1 to 2 of 2

Thread: "ZZ" and ^0

  1. #1

    "ZZ" and ^0



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi. Can somebody explain to me the formula below? I do understand what it does, but the "ZZ" and the '^0', not sure what it does. It will really help to understand it if somebody can talk me through it. Thanks

    =IF(COUNTIF(AJ$2:AJ2435,AJ2435)=1,IFERROR(LOOKUP("ZZ",REPT({"DR","BA"},SEARCH({"(DR)","(BA)"},E2435)^0)),""),"")

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    This part searches for either or your strings within E2435: SEARCH({"(DR)","(BA)"},E2435)
    IF one or both are found, then a number, representing the character position where found, is returned. If not found a #VALUE error is returned.
    The ^0 basically converts the numeric results to 1 so that REPT() in the next step shows only one rept (if any) of the string found.

    so if in E2435 you have string: xx(BA)dx

    The SEARCH({"(DR)","(BA)"},E2435) will return array {#VALUE,3}
    The ^0 converts that to {#VALUE,1}

    The REPT({"DR","BA"} will now replace the 1's in the SEARCH result array with the corresponding strings.

    so you get new array {#VALUE,"BA"}

    next the LOOKUP("ZZ", part looks for "ZZ" in that array {#VALUE,"BA"}. LOOKUP looks for the last occurrence in an array that is smaller than or equal to the lookup value, ignoring errors. So LOOKUP is looking for "ZZ" and any other string that doesn't begin with ZZ is smaller, so the last string that matches is "BA". And that is returned as the final result.

    Hope it helps.


Posting Permissions

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