I don't completely understand the formula
It's actually quite simple and your reaction will be: "Of course, it's so
simple!".
The first thing that happens is the COUNTIF checks each sheet to see if the
lookup_value exists on any of the sheets.
COUNTIF(Sheet1!A2:A200,A1)>0
COUNTIF(Sheet2!A2:A200,A1)>0
COUNTIF(Sheet3!A2:A200,A1)>0
etc
etc
If the lookup_value exists on any of the sheets then one of the above
expressions will return TRUE:
COUNTIF(Sheet1!A2:A200,A1)>0 = FALSE
COUNTIF(Sheet2!A2:A200,A1)>0 = FALSE
COUNTIF(Sheet3!A2:A200,A1)>0 = TRUE
etc
etc
The double unary -- will convert the logical TRUE or FALSE to 1 or 0
respectively:
--(COUNTIF(Sheet1!A2:A200,A1)>0) = 0
--(COUNTIF(Sheet2!A2:A200,A1)>0) = 0
--(COUNTIF(Sheet3!A2:A200,A1)>0) = 1
etc
etc
MATCH then looks for the first instance of 1 and if present returns it's
relative position within the array of results from the COUNTIF functions:
MATCH(1,{0;0;1},0) = 3 (the lookup_value 1 is found at relative position 3).
This result is then passed to the INDEX function:
INDEX({"Sheet1";"Sheet2";"Sheet3"},3)
That tells INDEX we want the 3rd value of the indexed array Sheet1, Sheet2,
Sheet3
INDEX({"Sheet1";"Sheet2";"Sheet3"},3) = Sheet3
This result is then added to a string of concatenation processes:
"'"&"Sheet3"&"'!A2:C200" = 'Sheet3'!A2:C200 (as a TEXT string)
Since we're "building" the range reference to be used in the VLOOKUP
function, the "built" reference will be a *TEXT* string that looks like a
valid range reference. So, we have to convert this *TEXT* string into a
valid range reference that the VLOOKUP can use. We do this using the
INDIRECT function.
INDIRECT("'"&"Sheet3"&"'!A2:C200") = 'Sheet3'!$A$2:$C$200 as a valid range
reference that VLOOKUP can use:
=VLOOKUP(A1,'Sheet3'!$A$2:$C$200,2,0)
exp101
