Find match in multiple numbers in one cell

[BeRol]

Dear community,

I am trying to set up a formula to do the following:

I have a list of customers with a certain number of work orders.

I have a list of invoices for each customer, listing a subset of work orders, i.e. not all work orders have been billed.

The Question: I want to find out which work orders have been billed.

Problem: in the invoices list, the work orders are all listed in one cell, separated by "|". The customer / work order list ist one item per line.

We are talking about 22000 work orders and 3000 invoices comprising one or several work orders.

The attached sample file gives an indication of the problem.

Thank you very much for any insight you might have.

[#2 · Accepted solution]

@BeRol 

You may try something like this...

On Workorders Sheet,

In C2

=IF(ISNUMBER(SEARCH("| "&TEXT(B2,"000")&" |"," | "&INDEX(Invoices!B:B,MATCH(A2,Invoices!A:A,0))&" |")),"Billed","Not Billed")

and then copy it down.

[#3]

@Subodh_Tiwari_sktneer 

Thank you for your speedy reply. After transposing your formula to the German Excel-Version, I only get "not billed" as a result. hmmm... I suspect, there might be a problem with the "|", since the first value does not have a leading "|".

Whoever desigend this output...

Cheers

Ben

[#4]

EUREKA! I found it. I failed to translate one command key word.

Thanks again.

[#5]

@BeRol 

Great! Glad it worked as desired.

Please take a minute to accept the post with the proposed formula as an Answer to mark your question as Solved. Also, you may hit the Like button if you liked the proposed answer.