Set a flag again messages logs against list of automated messages

28 Views Asked by At

I was wondering if someone would be able to help me out here with a formula I need to put together with an Excel workbook. I can usually pretty much answer any issues I have by look through here but I am stumped on this one as to how I should implement this.

Basically I have a log of messages I take from another system and then place them into an Excel Workbook.

Some of these will be automatic messages that we can send which will come from a list of around 80 messages.

Below is an brief example of the two spreadsheets that I would have

Message Logs

http://s14.postimg.org/4o8iyi469/Messages.png

List of Automated Messages

http://s30.postimg.org/b1184al5t/Automated.png

From the messages logs all messages will be attached with a 5 digit identifier which won't always be the same.

I tried numerous way such as using VLOOKUP INDEX etc

I'm assuming I would have to INDEX the automated messages within the formula and then try to partially match.

I would be grateful for any help on this one I've pretty much worn my keys out Google searching on this.

1

There are 1 best solutions below

0
On

I've managed to find a solution by using the following formula

by indexing and looking up the first 29 characters

=IF(ISNA(INDEX(LEFT(AUTOMSG!$A$1:$A$166,29),MATCH(LEFT(F2,29), INDEX(LEFT(AUTOMSG!$A$1:$A$166,29),0),0))),"No", "Yes")