Match() - Offset() - Index() Help

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Often I see mention of Offset(), Match(), & Index() XL functions.

Could someone big-picture these functions for me?

I have not used these functions yet in data lookup or evaluation. Shame
on me.

That said, The concept of "Index" I understand from db work. "Match"
seems vaguely logical to me for "lookup" work. "Offset" means nothing to
me yet.

Reading the XL help or reference books do not give me typical uses and
why's of the functions.

My focus is the accounting/financial world.

Using XP Pro & XL XP Pro SP1.

Dennis
 
This file brings together the three functions you are asking about to create
a Lotus 123 function that doesn't appear in Excel. I hope this can provide
an insight for you into the way these functions are used:
http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Dennis said:
Often I see mention of Offset(), Match(), & Index() XL functions.

Could someone big-picture these functions for me?

I have not used these functions yet in data lookup or evaluation. Shame
on me.

That said, The concept of "Index" I understand from db work. "Match"
seems vaguely logical to me for "lookup" work. "Offset" means nothing to
me yet.

Reading the XL help or reference books do not give me typical uses and
why's of the functions.

My focus is the accounting/financial world.

Using XP Pro & XL XP Pro SP1.

Dennis
Click to expand...

Basically, MATCH and INDEX are complimentary functions (that is, they do the
opposite of each other).
MATCH takes a value and finds where in a range that value is to be found.
For example, if it finds a match in the third cell of the range it returns
3.
INDEX takes a position and returns the value found in that position of a
range. For example, given the position 3 (meaning third), it returns the
value from the third cell in the range.

OFFSET is a bit like a vertical and horizontal lookup in a table. Suppose
you have numbers in a (4x4) table occupying C2:F5. The formula
=OFFSET(C2,1,2)
will return the value in cell E3 (that is, 1 row down and 2 columns across
from C2).
It is most useful if you want to calculate the row and/or column offsets, as
you just replace the 1 and/or 2 in the formula with the functions that do
the calculations you want.
It can have fourth and fifth parameters, in which case it will return an
array of values rather than an individual value. For example,
=SUM(OFFSET(C2,1,2,3,2))
will add up the values in the range E3:F5 (that is, starting as in the
example above but 3 rows high by 2 columns wide).

A simple (but very useful) example of the use of OFFSET is this.
Suppose you have payment figures down a column, say A1:A10, with the formula
=SUM(A1:A10)
to calculate the total in A11. Suppose you now want to insert an extra row
for an eleventh payment. If you insert it between rows within the range, the
formula will adjust to give the correct new total. However, if you insert it
after row 10, the formula will not adjust and the new payment will not be
added into the total.
If instead you had used the formula
=SUM(A1:OFFSET(A11,-1,0))
(that is, "sum the range from A1 to the cell above the one where the formula
resides"), the formula would adjust and your total would be correct after
inserting the new row after row 10.
 
Back
Top