# Thread: Lookup Text in a Range

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

TIA

cheers, francis

2. [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?

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

3. [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]

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

#### Posting Permissions

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