Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excel 2003

    Hi

    Cell A10 of the attached have this formula which return correctly the text contain in a cell of the range
    However, am not sure if this is a valid formula?

    =LOOKUP("text",A1:A9)

    If I change the text to tv3 in the cell A4, its return #N/A, why is this so?

    Need your expertise on this

    TIA

    cheers, francis
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='769255' date='05-Apr-2009 14:31']Excel 2003

    Hi

    Cell A10 of the attached have this formula which return correctly the text contain in a cell of the range
    However, am not sure if this is a valid formula?

    =LOOKUP("text",A1:A9)

    If I change the text to tv3 in the cell A4, its return #N/A, why is this so?

    Need your expertise on this

    TIA

    cheers, francis[/quote]

    LOOKUP requires that the range it applies to is sorted in ascending order. This function most probably uses a variant of binary serach.

    How that works is described in a graphical manner in the following attempt:

    http://tinyurl.com/czkket
    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Aladin Akyurek' post='769282' date='06-Apr-2009 02:26']LOOKUP requires that the range it applies to is sorted in ascending order. This function most probably uses a variant of binary serach.

    How that works is described in a graphical manner in the following attempt:

    http://tinyurl.com/czkket[/quote]

    Hi Aladin

    Thanks for the explanation and the link.

    I realised that the formula produced an error if I changed the text in the cell to "TV3" due
    to the "TV3" is larger than "ISTEXT" in the formula.

    For others, a more robust formula should be
    =LOOKUP(REPT("Z",255),A1:A10) where REPT("Z", 255) is the possible largest text

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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