Nested vlookup with possible if statement

I have an invoice spreadsheet that has several segments of billing data. I need to lookup 1 piece of the segment to another table and pull in the commodity description to a commodity column. I thought of delimiting the billing data but the issue is all segments are separated by dashes and some of the location names have dashes in them so the data won't delimit correctly. Attached is a sample of what I have.

For this example I want to lookup the commodity in column A of the data spreadsheet, find the match in column A of the commodity tab, and bring the value from column B of the commodity tab to column B of the data tab.

Thanks for your help.

Unfortunately your post does not comply with Rule 1 of our Forum, and with 11 posts you should know that. RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Enter this formula in B2 and copy down

Formula:

=IF(A2<>"",VLOOKUP(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",50)),50*2))," ",REPT(" ",50)),50))&"*",Commodity!$A$2:$A$5,1,0),"")

v	A	B
1	Billing	Commodity
2	21 FITZ STREET-COMBINED-C--R&M Exp - Supplies - Bulb/Lamp-613050-REPAIRS MAINT EXP_	613050 R&M Exp - Bulb/Lamp
3	118-122 WEST 23RD STREET-C--R&M Exp - Supplies - Plumbing-613000-REPAIRS MAINT EXP_	613000 R&M Exp - Plumbing
4	BOSTON BAY-C--Turn Exp - Keys & Locks-612050-TURNOVER EXP_	612050 Turn Exp - Keys & Locks

Thank you for the feedback