Looking up Specific Criteria in line item and doing a LookUP

[usama5788]

Can you please assist in helping with my Transaction memo line items below. Please also see ID name and ID number below. I want to be able to have some sort of formula that can look into my Transaction memo and pick up the Vendor ID and its Vendor Name. As you can see though that not all Memo transactions formatting is not the same. And I cannot find any consistency within each line item. The only thing that is consistent is that Vendor ID# contains 10 digits and also the word "AmericanExpress"

Can you please help since I have 100s of line items that are similar to this.

Please see attachment.

[Glenn Kennedy]

Are you sure that you have quoted the correct ID No for LNID and IC Tickets???

I used this to extract the 10 digit numbers:

=IFERROR(LOOKUP(10^10,MID(SUBSTITUTE(A2," ","a"),ROW(INDIRECT("1:"&LEN(A2)-9)),10)/ISERROR(--MID(" "&A2,ROW(INDIRECT("1:"&LEN(A2)-9)),1))/ISERROR(--MID(A2&" ",ROW(INDIRECT("11:"&LEN(A2)+1)),1))),"").

This can then be used to lookup the company name, like this:

=INDEX($A$12:$A$16,MATCH(IFERROR(LOOKUP(10^10,MID(SUBSTITUTE(A2," ","a"),ROW(INDIRECT("1:"&LEN(A2)-9)),10)
/ISERROR(--MID(" "&A2,ROW(INDIRECT("1:"&LEN(A2)-9)),1))/ISERROR(--MID(A2&" ",ROW(INDIRECT("11:"&LEN(A2)+1)),1))),""),$B$12:$B$16,0))

But it returns two errors, where I think you have provided an incorrect 10 digit number...

[usama5788]

Please see attachment. As you can see the formatting for each line description is not same and that is where I am having problems. Like I do see the same ID code but since there is a space in between some it does not get picked up.

[Glenn Kennedy]

Do the strings always end with Addenda: No Addenda??

[Glenn Kennedy]

If so...

=INDEX($A$12:$A$16,MATCH(--RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"Addenda: No Addenda","")," ",""),10),$B$12:$B$16,0))

[Glenn Kennedy]

Last shot. Assuming the worst... this will also work with Addenda any_text_to_follow

=INDEX($A$12:$A$16,MATCH(--RIGHT(SUBSTITUTE(LEFT(A2,SEARCH("Addenda",A2)-1)," ",""),10),$B$12:$B$16,0))

[usama5788]

Thank you Glen both work perfect.

Is it possible if you can explain a little about in lemain terms on what is exactly happening in this formula. (=INDEX(Sheet2!$E$2:$E$72,MATCH(--RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"Addenda: No Addenda","")," ",""),10),Sheet2!$F$2:$F$72,0))