Lotus @xindex equivelant in Excel

M

Monty1952

Does anyone know what the equivelant is of the Lotus @xindex function? Its
the function that in effect allows you to do a vertical and horizontal lookup
of a table and give you the value of that intersection.

Thanks in advance
 
T

Tim C

If you are just supplying row and column numbers (rather than matching to
row and column headings) use OFFSET:

=OFFSET(A1,RowNumber,ColumnNumber)

Note that RowNumber and ColumnNumber will be the number of spaces the target
cell is offset from the reference cell.

In other words,

@XINDEX(B2:B10,2,3)

would be translated as

OFFSET(B2,1,2)

or

OFFSET(A1,2,3)

Tim C
 
A

Andy

This file might be a help:
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.

There is also an alternative construction using SUMPRODUCT at:
http://www.bygsoftware.com/Excel/functions/sumproduct.htm

Andy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top