Lookup Text in a Range

Topic

New Reply

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

Reply | Quote

Replies

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

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

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

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

Reply | Quote