Agreed on virtually all counts. Mea culpa, mea culpa, mea maxima culpa!
And thank you for posting such an elegant and dependable formula (my
comment re fragility really applied to the H and VLOOKUPs, not to
LOOKUP, since it does not use the column index number, which is usually
given in VLOOKUP examples as an actual number as opposed to a variable
expression) - I'm afraid I responded in haste when I should have taken
more time, and I should certainly have confined my response to the
functions I use and know. I am lashing myself with the proverbial wet
noodles, and have definitely learned something from you (and from
Aladin's post below), both technically and otherwise.
Maybe I can learn some more vis-a-vis the merits/demerits of VLOOKUP,
since we may differ somewhat on its merits. It appears to me that
VLOOKUP can be more efficient and useful than INDEX/MATCH when:
(1) you are certain that neither you nor anyone else will ever move the
target column to the left of the search column (e.g. the search column
will ALWAYS be the first column of the table or list, as you suggest),
and
(2) you use an expression such as
COLUMN(Target)-COLUMN(Search)+1
for the column index number, to make it adapt to column insertions
between the search and target column, (which is how many people seem to
expand a table to accommodate more columnar values), and
(3) you are extracting target values from no more than two or three
columns in the table for each criterion value, as opposed to extracting
many values from different target columns for each criterion or search
argument value, all of which can be extracted using one MATCH function
on the search argument, plus one INDEX function per value extracted.
In all likelihood, a MATCH and a VLOOKUP with similar parameters are
similar in time cost, with a VLOOKUP being slightly more expensive
because it must also index into the target column, but it should do so
less expensively than the INDEX function. However the INDEX function
itself should be significantly less expensive than a VLOOKUP since it
does not need to search. Therefore, for a given table depth, there
should be a crossover in efficiency at some number of values retrieved
per criterion (3,4,?), when, for n values retrieved, the cost for n
VLOOKUPs becomes greater than the cost for one MATCH plus n INDEXes. If
you test for absence of the search criterion, the time benefits of
INDEX/MATCH could be greater, depending on how you test. (I wonder if
anyone has benchmarked these two approaches?)
If any of 1, 2, or 3 are untrue, it appears to me that you are probably
better off using INDEX and MATCH, either in one formula or separately,
as appropriate, even though you may trade some efficiency for
maintainability.
You are clearly more knowlegeable on XL than I, and I would be happy to
know if and where my thinking is awry on this issue.
