Tuesday, October 3, 2017

yogi_Search Column D For Specified Entries And Key-in Corresponding Entries In Column E

Google Spreadsheet   Post  #2256

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-03-2017
question by: Osv4
Non Exact Matching and FIND Function
Hi All,

I am building a client database. This really should be a software that does this but I am stuck with Google Sheets as that is where the data is stored making things a little more complex when it comes to functionality. I was actually very surprised on how much can be done with Sheets (and excel).

Anyway, there is a list of clients, dates, contact details and so on in my Sheet. There is also a column for Comments and Current Status. This is where the fun begins.

In our comments, we write things like Extension, terminated trial, returned product and so on. They are also followed by more detailed comments within the same cell. This information is then pulled into the main follow up sheet from multiple ones on a month by month basis. What I need to happen is:

Cell D1 - Row 1 to 5
Extension. Product not suitable
Extension. Client not sure yet
Non compliant. Not interested 
Terminated. Could not use it
Returned. Not required

Extended or Terminated or Non-Compliant or Returned

Basically, what I need to happen is a search in D1, D2, D3, D4, D5.....to find the suitable word and display a form of it in 'Current Status' E1, E2, E3 and etc....

So far I have used the formula in =IF(FIND("Extension",D2)>0,"Extended","No")

This actually works great and finds the word extension, but if I can't seem to be able to add multiple conditions and make the IFS and AND work properly without producing errors. It has to be a multi condition search. There are also some cells in Column D that are Blank, for which the formula spits an error. So I probably need to add ISBLANK or IFERROR into this. I wanted to use IFERROR for labeling things with Unknown in E so that I can identify if someone used the wrong word or misspelled it.

Thanks in advance!!

No comments:

Post a Comment