VLOOKUP IF Cell Contains a Word within Text
 

Hi,
Can anyone advise me how I do a VLOOKUP if a cell Contains a certain word.
The cells contain various sizes and either Standard, Secure or Secure Plus.
i.e.

4Mbit/s Standard
4Mbit/s Secure
4Mbit/s Secure Plus
6Mbit/s Standard
6Mbit/s Secure
6Mbit/s Secure Plus
8Mbit/s Standard
8Mbit/s Secure
8Mbit/s Secure Plus
10Mbit/s Standard
10Mbit/s Secure
10Mbit/s Secure Plus
15Mbit/s Standard
15Mbit/s Secure

What I need to do is if the cell selected contains say "Secure Plus", then
do a lookup.

Thanks

DAWN

Answer: VLOOKUP IF Cell Contains a Word within Text
 

Hi Dawn,

You can use a combination of the IF and VLOOKUP functions to achieve this. Here are the steps:

1. In a new column, use the following formula to check if the cell contains the word "Secure Plus":
   
   Formula:

   ---

   =IF(ISNUMBER(SEARCH("Secure Plus",A2)),"Yes","No") 


   ---

   Note: Replace A2 with the cell reference of the first cell in your data range.
   
2. Copy the formula down to all the cells in the column.
   
3. Now, you can use the VLOOKUP function to lookup the value you need based on the "Yes" or "No" result in the new column. Here's an example formula:
   
   Formula:

   ---

   =VLOOKUP("Yes",B:C,2,FALSE) 


   ---

   Note: Replace B:C with the column range that contains your data and the lookup value, and adjust the column number (2 in this example) to match the column number of the value you want to return.

Hi Dawn
One way, with data in A1
=IF(ISERROR(SEARCH("Secure Plus",A1)),"",your_lookup_formula)


--
Regards

Roger Govier


"DAWN" wrote in message
...
Hi,
Can anyone advise me how I do a VLOOKUP if a cell Contains a certain word.
The cells contain various sizes and either Standard, Secure or Secure
Plus.
i.e.

4Mbit/s Standard
4Mbit/s Secure
4Mbit/s Secure Plus
6Mbit/s Standard
6Mbit/s Secure
6Mbit/s Secure Plus
8Mbit/s Standard
8Mbit/s Secure
8Mbit/s Secure Plus
10Mbit/s Standard
10Mbit/s Secure
10Mbit/s Secure Plus
15Mbit/s Standard
15Mbit/s Secure

What I need to do is if the cell selected contains say "Secure Plus", then
do a lookup.

Thanks

DAWN

One method:

=IF(ISERR(SEARCH("Secure Plus",A1)),"",VLOOKUP(A1,E1:F12,2))





DAWN Wrote:
Hi,
Can anyone advise me how I do a VLOOKUP if a cell Contains a certain
word.
The cells contain various sizes and either Standard, Secure or Secure
Plus.
i.e.

4Mbit/s Standard
4Mbit/s Secure
4Mbit/s Secure Plus
6Mbit/s Standard
6Mbit/s Secure
6Mbit/s Secure Plus
8Mbit/s Standard
8Mbit/s Secure
8Mbit/s Secure Plus
10Mbit/s Standard
10Mbit/s Secure
10Mbit/s Secure Plus
15Mbit/s Standard
15Mbit/s Secure

What I need to do is if the cell selected contains say "Secure Plus",
then
do a lookup.

Thanks

DAWN


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=399407