"ZZ" and ^0

toony

New member
Joined
Mar 6, 2014
Messages
49
Reaction score
0
Points
0
Location
UK
Excel Version(s)
Excel 2016
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)),""),"")
 
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.
 
Back
Top