Friday, March 30, 2012

yogi_Check For Partial Match In One Cell And Anothe Cell To Be Non-Blank

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #491

user tobiasgMC said: 
Partial Match in IF Statement I am trying to create a formula that will look for any instance of AB or LOA in one cell and if one of those is present then will check a second cell for "X" or notnull would be fine and finally spit out a 1 if everything is true. 
I could actually do this the other way around, look for X first and then match. . . The part I seem to have the most trouble with - partial match - I can get it to work in a =COUNTIF(a2:a20, "AB*") but I cannot get that to work in an IF statement. Some cells contain AB2 or LOA(germany) etc. 
Any advice would be appreciated. Using Chrome 17 on Windows 7 
Here is a sample spreadsheet 
I need a formula in J4 that checks to see if LOA,AB, or is blank in column C. Then checks Deposit column for X, then returns LX(if LOA and X), LA(if AB and X), P(if blank and X) finally if both columns are blank a "?" would be nice. LOA AND AB matches must be partial/fuzzy as other info is often in those cells. Any help you could provide would be wonderful. My end goal is to then count each of these at the bottom of column J. 
Thank you, 

 following is a solution to the problem