Vlookup "contains"
 

i'm trying to write a vlookup that will advise me when a traveller has
visited an airport in belgium..

e.g. the cell will contain something like LHR/CDG/ODL or ODL/LGW/SEA

Basically in the example above I want it to show me when the cell contains
ODL (or another Belgium airport code which i have a list of, hence the
vlookup).

Can this be done, so far I have the following...

=IF(ISERROR(VLOOKUP(C2,'Belgium Codes'!C1:C15,1,0)),"","Belgium")

but this only returns when the cell purely contains "ODL"..

Can anyone help me please?

Thanks,

Vlookup "contains"
 

Hi Louise

One way would be to use an array formula like the following in cell B1
{=ISNUMBER(FIND(E$1:E$10,A1))}

where E1:E10 contains your list of airports, and column A contains your
string to be searched
Copy down column B for the extent of your list in column A

Array formulae must be committed or Edited using Ctrl+Shift+Enter (CSE)
not just Enter.
When you use CSE, Excel will create the curly braces { } for you.
Do not type them yourself.

--
Regards

Roger Govier


"Louise" wrote in message
...
i'm trying to write a vlookup that will advise me when a traveller has
visited an airport in belgium..

e.g. the cell will contain something like LHR/CDG/ODL or ODL/LGW/SEA

Basically in the example above I want it to show me when the cell
contains
ODL (or another Belgium airport code which i have a list of, hence the
vlookup).

Can this be done, so far I have the following...

=IF(ISERROR(VLOOKUP(C2,'Belgium Codes'!C1:C15,1,0)),"","Belgium")

but this only returns when the cell purely contains "ODL"..

Can anyone help me please?

Thanks,