Xindex

[denny]

When I was using Lotus 123 I used a function called Xindex that would find
the intersection in a named ranged. Is there a function in Excel that will
do the same.

Example: @xindex(range,a1,"actual sales")

Where the range was a named range and cell a1 contained the salespersons
name I entered and "actual sales" was the column heading for the sales which
was in the first fow of the named range

 

[B. R.Ramachandran]

Hi,

One way:

=INDEX(actualsales,MATCH(A1,range,0))

where "range" and "actualsales" are the names of the ranges as you have
described in your post.

Another way:

=VLOOKUP(A1,A2:B101,2)

Here, A2:B101 is the entire range containing data, where Column A contains
names and Col B contains sales.
The above formula returns the intersection of the row containing "A1" in the
left-most column (that is Column A) and the second column (the "2" in the
formula stands for that) which is Column B.

If this helps, please give a feedback by clicking "Yes".

Regards,
B. R. Ramachandran

 

[B. R.Ramachandran]

Hi,

I am sorry that there were a couple of typos in my formulas.

The first formula should be,

=INDEX(actualsales,MATCH(C1,range,0))

where you input a name in C1. The formula will return the actualsales value
that corresponds to the intersection the name column (where it matches with
the name you have entered in C1) and the salesvalue column.

The second formula should correspondingly read,

=VLOOKUP(C1,A2:B101,2) where A2:101 is the entire data range.

Sorry about the typos.

B. R. Ramachandran