# Lotus Equivalent

1. ## Lotus Equivalent

Would XL 2000 have an equivalent to the Lotus function "@XINDEX"?
@XINDEX returns a value at the intersection of row and column titles.

e.g. @XINDEX(Range,"Salary",A4) returns \$200,050.00 (I wish)

The value in A4 = Brian.

To those of you who responded to my question on GROUPS ........ I got it!
Takes me a while 'cos I still think Lotus. Be patient. Best regards

2. ## Re: Lotus Equivalent

It would be much easier if you specified what Range refers to and the
range where "Salary" occurs.

Brian Keanie wrote:
> Would XL 2000 have an equivalent to the Lotus function "@XINDEX"?
> @XINDEX returns a value at the intersection of row and column titles.
>
> e.g. @XINDEX(Range,"Salary",A4) returns \$200,050.00 (I wish)
>
> The value in A4 = Brian.
>
> To those of you who responded to my question on GROUPS ........ I got it!
> Takes me a while 'cos I still think Lotus. Be patient. Best regards
>
>

3. ## Re: Lotus Equivalent

>It would be much easier if you specified what Range refers to and the
>range where "Salary" occurs.

No it wouldn't. OP's question is crystal clear if you know 123. If you don't
know 123, perhaps you should let those who do respond.

>Brian Keanie wrote:
>>Would XL 2000 have an equivalent to the Lotus function "@XINDEX"?
>>@XINDEX returns a value at the intersection of row and column titles.
>>
>>e.g. @XINDEX(Range,"Salary",A4) returns \$200,050.00 (I wish)
>>
>>The value in A4 = Brian.

....

Excel doesn't have an equivalent for 123's @XINDEX (more of a double lookup
than an index operation). The following are the Excel equivalents.

VLOOKUP(A4,Range,MATCH("Salary",INDEX(Range,1,0),0),0)

HLOOKUP("Salary",Range,MATCH(A4,INDEX(Range,0,1),0),0)

INDEX(Range,MATCH(A4,INDEX(Range,0,1),0),MATCH("Salary",INDEX(Range,1,0),0))

4. ## Re: Lotus Equivalent

Harlan Grove wrote:
>
>>It would be much easier if you specified what Range refers to and the
>>range where "Salary" occurs.

>
>
> No it wouldn't. OP's question is crystal clear if you know 123. If you don't
> know 123, perhaps you should let those who do respond.
>

[...]

I don't think that would count as a prerequisite in this particular case.

> Excel doesn't have an equivalent for 123's @XINDEX (more of a double lookup
> than an index operation). The following are the Excel equivalents.
>
> VLOOKUP(A4,Range,MATCH("Salary",INDEX(Range,1,0),0),0)
>
> HLOOKUP("Salary",Range,MATCH(A4,INDEX(Range,0,1),0),0)
>
> INDEX(Range,MATCH(A4,INDEX(Range,0,1),0),MATCH("Salary",INDEX(Range,1,0),0))
>
>

I did not list these choices simply in order to avoid all those inner
INDEX calls. Another reason is that I dislike the idea of including the
match ranges in the specification of Range.

5. ## Re: Lotus Equivalent

Sorry.

Name, Position, Salary
Greg, ***'t, 40
Brian, Sales, 20
Paul, Mfg,60
The above is "Range" lets say P1:R4
Cell A4 contains the name "Brian".
Cell A5 contains the XINDEX formula and in the above case would return 20.

Hope this is clearer. Regards

"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41D865F4.9010105@xs4all.nl...
> It would be much easier if you specified what Range refers to and the
> range where "Salary" occurs.
>
> Brian Keanie wrote:
> > Would XL 2000 have an equivalent to the Lotus function "@XINDEX"?
> > @XINDEX returns a value at the intersection of row and column titles.
> >
> > e.g. @XINDEX(Range,"Salary",A4) returns \$200,050.00 (I wish)
> >
> > The value in A4 = Brian.
> >
> > To those of you who responded to my question on GROUPS ........ I got

it!
> > Takes me a while 'cos I still think Lotus. Be patient. Best regards
> >
> >

6. ## Re: Lotus Equivalent

There is nothing to be sorry about. In this case one way is to invoke a
formula with VLOOKUP()...

VLOOKUP(LookupValue,LookupTable,ColIdx,MatchType)

which filled in becomes:

=VLOOKUP(A4,\$P\$2:\$R\$4,3,0)

The MatchType is set to 0 in order to force VLOOKUP to execute an exact
match between the value in A4 and the values in the first column of
P2:R4. The ColIdx is set to 3 for it heads the 3rd column of P2:R4.

For the exact translations of XINDEX, see Harlan's post (along with my

Brian Keanie wrote:
> Sorry.
>
> Name, Position, Salary
> Greg, ***'t, 40
> Brian, Sales, 20
> Paul, Mfg,60
> The above is "Range" lets say P1:R4
> Cell A4 contains the name "Brian".
> Cell A5 contains the XINDEX formula and in the above case would return 20.
>
> Hope this is clearer. Regards
>
> "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
> news:41D865F4.9010105@xs4all.nl...
>
>>It would be much easier if you specified what Range refers to and the
>>range where "Salary" occurs.
>>
>>Brian Keanie wrote:
>>
>>>Would XL 2000 have an equivalent to the Lotus function "@XINDEX"?
>>>@XINDEX returns a value at the intersection of row and column titles.
>>>
>>>e.g. @XINDEX(Range,"Salary",A4) returns \$200,050.00 (I wish)
>>>
>>>The value in A4 = Brian.
>>>
>>>To those of you who responded to my question on GROUPS ........ I got

>
> it!
>
>>>Takes me a while 'cos I still think Lotus. Be patient. Best regards
>>>
>>>

>
>
>

7. ## Re: Lotus Equivalent

>Harlan Grove wrote:

....
>>Excel doesn't have an equivalent for 123's @XINDEX (more of a double

lookup
>>than an index operation). The following are the Excel equivalents.
>>
>>VLOOKUP(A4,Range,MATCH("Salary",INDEX(Range,1,0),0),0)
>>
>>HLOOKUP("Salary",Range,MATCH(A4,INDEX(Range,0,1),0),0)
>>
>>INDEX(Range,MATCH(A4,INDEX(Range,0,1),0),
>>MATCH("Salary",INDEX(Range,1,0),0))

>
>I did not list these choices simply in order to avoid all those inner
>INDEX calls. Another reason is that I dislike the idea of including the
>match ranges in the specification of Range.

If the OP were dealing with static column headings, then @XINDEX wasn't the
right choice in 123. If neither column nor row headings were static, then
you can't hardcode either row or column index in HLOOKUP or VLOOKUP,
respectively, in thich case those unloved match ranges would need to be
referenced inside MATCH calls.

Also, idiot-proofing requires including the match ranges with the data
ranges. If the range contains headings in the leftmost column and top row as
well as filler rightmost column and bottom row (empty but shaded or
containing | and _), then rows and columns may be inserted or deleted at
will inside the range and the formulas I gave will always work. If you
specify only the data, you can't insert columns immediately to the right of
the leftmost column or row headings or immediately below the top row of

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1