Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
DAWN
 
Posts: n/a
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Roger Govier
 
Posts: n/a
Default

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



  #4   Report Post  
Bryan Hessey
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
text to appear at top of cell jenniss New Users to Excel 4 August 30th 05 02:08 AM
Wrap text ceases to function in Excel if cell exceeds 9 lines nycemail Excel Worksheet Functions 1 July 24th 05 06:09 PM
Split Long Text Cell into Two Shorter Cells Without Splitting Word Naomi T Excel Discussion (Misc queries) 1 July 7th 05 06:49 AM
Wrap text limits in Excel 2003 cell formatting Adelrose Excel Discussion (Misc queries) 1 April 19th 05 06:32 PM
Cell set to wrap text and blank line -- fix? Fred Holmes Excel Discussion (Misc queries) 0 January 25th 05 08:10 PM


All times are GMT +1. The time now is 02:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"