Tech Support Guy banner
Status
Not open for further replies.

Excel formula query

Solved 
Tags
vlookup
652 views 11 replies 2 participants last post by  etaf 
#1 ·
Hi guys

I have a report and in the report I have 7 columns, in column B I have a description of product which is quite lengthy, is it possible to show all that contain "UNLIPPED" AND "900", and also "UNLIPPED" and "598"
#VLOOKUPSCARESME

thanks for reading
 
#3 ·
I would like to identify all "UNLIPPED" AND "900", and also "UNLIPPED" and "598" so in column B a cell will either contain UNLIPPED & 900 OR UNLIPPED & 598, all other products are irrelevant, if that makes sense?

thanks
 

Attachments

#6 ·
i have used a search case insensitive & wildcards accepted and IF to give a YES or NO output
=IF(ISERROR(SEARCH("*UNLIPPED*900*",B1,1)),IF(ISERROR(SEARCH("*UNLIPPED*598*",B1,1)),"NO","YES"),"YES")
I also added the numbers in a couple of cells just for simplicity to see the result
You could change the output from YES , NO to whatever you wanted
 

Attachments

#9 ·
actually :D :D it doesn't really do what I expected :D
the cell must contain both UNLIPPED AND 900 or UNLIPPED AND 598, currently it will return UNLIPPED OR 598 OR 900 as a true value
 
#10 ·
can you highlight in the excel the lines that are wrong
The IF
=IF(ISERROR(SEARCH("*UNLIPPED*900*",B1,1)),IF(ISERROR(SEARCH("*UNLIPPED*598*",B1,1)),"NO","YES"),"YES")

the First IF
ISERROR(SEARCH("*UNLIPPED*900*",B1,1)
looks for UNLIPPED and 900
so if it cannot find , then its TRUE - if it finds, then returns a YES

If it cant find that string then
ISERROR(SEARCH("*UNLIPPED*598*",B1,1))
Checks for
looks for UNLIPPED and 598
so if it cannot find , then its TRUE , so NO - if it finds, then returns a YES

At least that what I had hoped the formula did
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top