+ Reply to Thread
Results 1 to 7 of 7

Lotus Equivalent

  1. #1
    Brian Keanie
    Guest

    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. #2
    Aladin Akyurek
    Guest

    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. #3
    Harlan Grove
    Guest

    Re: Lotus Equivalent

    "Aladin Akyurek" <[email protected]> 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.

    >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. #4
    Aladin Akyurek
    Guest

    Re: Lotus Equivalent

    Harlan Grove wrote:
    > "Aladin Akyurek" <[email protected]> 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. #5
    Brian Keanie
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #6
    Aladin Akyurek
    Guest

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

    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" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >>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. #7
    Harlan Grove
    Guest

    Re: Lotus Equivalent

    "Aladin Akyurek" <[email protected]> wrote...
    >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
    column headings.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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