Looking Up Part of a Text String in a Table

[ExcelDon2018]

Hi all,

Newbie here. I've browsed here previously but never posted - I've scoured the internet for potential solutions to this problem but so far have had no joy. Hoping you geniuses can help.

Basically, I have a document I use to ensure transactions between an array of entities are classified correctly. In order to be classified, there needs to be pre-determined text in the transaction reference.

Let's say, for argument, the transaction references are *GENEXP*, *SALARY*, *PROJEXP*, *GRANTEXP* and so on...

In the current form, the document has a column for each of these references with the formula =IF(IFERROR(VLOOKUP(O3,L4,1,FALSE),0)=0,0,1)) where O3 is the text string, e.g. *GENEXP*, and L4 is the transaction reference e.g. Movement of Salary Costs *SALARY*. Essentially, this assigns a 1 if the text string appears in the transaction reference and a 0 if not. These columns are then all added together and need to equal 1 (as we cannot have a transaction classified in more than one way e.g. Movement of salary costs *GENEXP* *SALARY*).

This is all well and good, however there are about 20 columns of text strings and in excess of 20,000 rows of transaction references meaning calculation takes close to half an hour.

In order to optimise this, I want to invert this if possible. So have a table of reference data which stores all of the text strings and then one single column which looks up (or even better SUMIF to ensure there is not more than one text string in the transaction reference) the transaction reference in this table.

So in effect, long story short (hope you're still following), we would be looking up Movement of Salary Costs *SALARY* in the table of reference data to see if any of the text strings are present.

Obviously, this is just a proposed method. If you can think of a better way of laying this out then please let me know. I'd like to steer clear of macros and the like as I want it to be as simplified and quick as possible.

Thanks in advance.

 

[Kamolga]

Hi,

Indeed I would usually set up my data in a table with a column "Type" and have Salary, Genexp, etc...so less column and more lines. It takes a bit of time (rather manually or writing a macro if this is an import that is recurrent) but it is worth having the data sheet properly set up.

To go through the data, I would then use a pivot table rather than formulae.