=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)
This tests to see if the result cannot be found (is N/A). If the first
VLOOKUP results in nothing (or N/A), then the test fails and "0" is returned
as your answer. Otherwise, the second VLOOKUP is run and the result is
returned.
VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data!$A$1:$C$1,IF($V1=2,-1,1)),))
This is the first test and should be broken down a bit to understand it.
First, it is searching for the content of cell A1 in another worksheet
(that worksheet's name is "Data") in column B, rows 1 through 200. Let's say
cell A1 has the word "Apple"...this searches for that word in Data!B1:B200.
Because it searches for one thing in a vertical column 200 rows tall, it is
called V (or Vertical) Lookup.
Once it finds the first instance of whatever is in cell A1, the VLOOKUP
will then return a value to the right of that value. So if it found "Apples"
in Data!B25, then it might return the value from Data!C25. Since the VLOOKUP
was limited to columns B & C (it said B1:C200), you cannot return the value
from, say, D25 or E25.
At the end of this particular VLOOKUP you'll notice a comma with nothing
after it, they just close that part of the formula out with closing
parentheses. That means the VLOOKUP is not necessarily looking for an exact
match. It will find the closest match it can in a SORTED list without going
over the desired result. So Data!B1:B200 must be sorted and it will find the
first instance that does not exceed your wish. So if it couldn't find
"Apples" but did find "Apple" in B25 and "Applesauce" in B26, it would stop
testing at B26 and say that B25 was the best match without going "over"
(because it assumes everything after B26 all start with higher letters, like
Bananas, Berries, Coconut, and so on in an alphabetized list).
You would assume that since the VLOOKUP was so limited to just columns B &
C, the user would just return the value from C (which you would just indicate
by typing ",1" to indicate C is 1 row to the right of column B). But they're
adding one more instruction which is not clear to me.
They are trying to match the data in cell A1 to the value in either
Data!A1, Data!B1, or Data!C1. The trick is that one of those 3 cells
probably does not equal the value of cell A1, so then they say if cell V1=
the number 2, then it wants the value from Data!A1:C1 that is the smallest
value greater than or equal to the data in cell A1. Otherwise, if V1 is not
equal to the number 2, it wants the largest value in Data!A1:C1 that is less
than or equal to the data in cell A1. Without more info, I cannot tell what
the point of this test is. Either the VLOOKUP returns the value from column
B (using ,0) or column C (using ,1), so this test is a little unusual. I'm
assuming Data!A1, Data!B1, and Data!C1 must have either 0 or 1 in each cell
(so we know if they want the value from B25 or C25), but I'm befuddled.
So now we've determined whether the first VLOOKUP returns a valid result or
not (honestly, the result itself does not matter...all we wanted to know is
that it does not come up with nothing, or N/A). No result returns "0".
Otherwise, we run the second VLOOKUP to give us the answer we're really after.
VLOOKUP(B1,Data!$B$1:$C$200,MATCH(A1,Data!$A$1:$C$1),)
So then we have the actual result we're probably hoping for in the first
place via a second (different) VLOOKUP. It tests to see if the data in cell
B1 (not A1) can be found in the sorted list of Data!B1:B200 (so just like
before, but instead of searching for "Apples", perhaps B1 says "Fuji" and
it's now searching for that in the same list it was searching the first time
around.
Again, if it finds the value of B1 in the list Data!B1:B200 without going
over, then we're limited to returning either the value from Data's column B
or column C. This time they've said find the largest value in Data!A1:C1
less than or equal to the value of cell A1...if that match returns a 0, we'll
pull the value from column B; if that match returns a 1, we'll pull the value
from column C; if that match returns anything else, you'll get an error. So
again, I'm stumped on the whole point of all the "MATCH" tests but with more
info we can probably help.
