CHOOSE
I finally got a chance to use one of those nice boutique functions from Excel: CHOOSE.
CHOOSE takes a one-based index as its first argument, followed by an unrestricted-length list of arguments covering the gamut of that index’s expected range. In other words, if you expected the index to range from 1 to 4, you would provide four arguments following the index. CHOOSE selects the index’th argument and returns it.
Now, nothing is ever completely simple, so my task was actually to report a different string of text depending on the state of two cells, either of which could be 0 or 1. Meaning: “00” = “No activity”, “10” = “Historical”, “01” = “New”, “11” = “Continuous”. One more typical Excel way of dealing with this is to make a mapping table with the aforementioned four options, then do a VLOOKUP. But to avoid that, you can use CHOOSE as long as you can boil those four 0/1 permutations into a single index.
Well, 0 and 1 look like binary, so you can kind of do a A1+2*B1+1, if your 0/1 cells are in A1 and B1 for example. This converts the different pairs of 0 and 1 into an index from 1 to 4. Bingo.
Then, it’s just: =CHOOSE(A1+2*B1+1,"No activity","Historical","New","Continuous")
No maps needed!