Lotus Equivalent

[Brian Keanie]

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

 

[Aladin Akyurek]

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

 

[Harlan Grove]

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.
....

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))

 

[Brian Keanie]

Sorry.

Name, Position, Salary
Greg, Ass'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]

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.

 

[Aladin Akyurek]

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
reply to his).

 

[Harlan Grove]

Harlan Grove wrote: ....

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
column headings.