Formula to auto-populate cost codes

[ksnl85]

Hi,

I'm trying to come up with a formula in which I can auto-populate a cost code into a column by searching for keywords in another column. A separate worksheet would contain all the descriptions and cost codes as reference for the search. The problem I'm having is that the data I'm searching through will have extra words and characters which are irrelevant to me, so it's not an exact match, and some of the descriptions fall under the same cost code. I just need to keep focus on the keywords in my description. I will then need to replicate that formula on multiple worksheets in the workbook and repeat the process on thousands of workbooks. I'm going to end up writing a macro to make the process more efficient but need to start with the basics first. Any help and/or suggestions would be appreciated.

I'll attach a sample because it's just easier to see it than for me to poorly explain it haha.

[JohnTopley]

In B2

=IFERROR(INDEX('Sample Cost codes'!$B$2:$B$13,MATCH(LEFT('Sample Tab'!$A2,FIND("(",'Sample Tab'!$A2)-2),'Sample Cost codes'!$A$2:$A$13,0)),"Not found")

Copy down

[Gregb11]

Similarly, since you're on MS365, you could try this:

=XLOOKUP(LEFT(A2,SEARCH(" (",A2)-1),'Sample Cost codes'!$A$2:$A$13,'Sample Cost codes'!$B$2:$B$13,"Not Found",0)

[ksnl85]

Both of those work for the most part. I'm getting "not found" in the cell with "orientation/training" even though "orientation" is listed in the cost codes tabs and the first word in that string. Any suggestions on what's happening there?

[JohnTopley]

The match is on the full string "ORIENTATION / TRAINING " which does match "Orientation".

[Gregb11]

This one is a little tricky because it's kind of going backwards. Since you're on MS365, try this formula in cell B2 and copy down:

=INDEX('Sample Cost codes'!$B$2:$B$13,MATCH(A2,XLOOKUP("*"&'Sample Cost codes'!$A$2:$A$13&"*",A2,A2,,2),0))

This will work even if the keywords are in the middle of the description. Probably not needed I'm guessing so if the Indirect labor will always start with the same words as in the Description, you could use this:

=INDEX('Sample Cost codes'!$B$2:$B$13,MATCH(A2,XLOOKUP('Sample Cost codes'!$A$2:$A$13&"*",A2,A2,,2),0))

[ksnl85]

I have a formula used to match key words in a column and based on that keyword, return a cost code. Not all descriptions will have a cost code so I'd like to return a "XXXX" or generic cost code to let me know it's a misc/unknown code. Right now it if doesn't match it returns a "0" and I can't seem to figure out how I can return "XXXX" if it's not found.

Here is the formula I'm using:

=XLOOKUP(TRUE, ISNUMBER(SEARCH('Cost Code Database'!$P:$P,A3)),'Cost Code Database'!$Q:$Q)

I've tried:

=XLOOKUP(TRUE, ISNUMBER(SEARCH('Cost Code Database'!$P:$P,A3)),'Cost Code Database'!$Q:$Q, "XXXX") for the [If_not_found] parameter and it still returns a "0" if it's not found.

Any help would be appreciated, Thanks!

[kvsrinivasamurthy]

In B2 then copy doewn.

Please Login or Register  to view this content.

[Gregb11]

To answer your question in Post#7, you could try this:

=LET(a,XLOOKUP(TRUE, ISNUMBER(SEARCH('Cost Code Database'!$P:$P,A3)),'Cost Code Database'!$Q:$Q),IF(a=0,"XXXX",a))

(works with 365 only)