Help on Formula Search, Find or Other one

[Chevaleridulys]

Hi guys,

First of all thank you for your hard work and all help you can share.
Basically this is something I am trying to do for work but can't seem to finish.

Okay, so we are creating a control. We ech day receive several requests that come with a number (Imagine it's LUNA12314654). So, we receive around 50 lines per day but not all of them are for my team.
so manually we retrieve the ones that are for us and I want to donwload every single line anad search for that numbers in the ones we manually retrieve. In the end we will only have to control the ones which are not for us.

So the problem is the following... Sometimes this request comes into two pages. So LUNA000001 and LUNA000002
And in our manually extraction we had something like LUNA00001_LUNA00002

i've tried using =IF(SERROR(A1,SHEET1!H:H,1,FALSE)),"Non Exist","Exist") and to the single documents he found and said Exist. But unfortunately this won't work for the ones we put LUNA00001_LUNA00002.
Can you help on this?

I tried the find, seach and can't find them..

Thank you all,
Paulo

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Chevaleridulys]

Sorry. I've attached as per instructions.

Thank you
PCG

[AliGW]

Where have you shown what you want?

[Chevaleridulys]

So in "F" column I am searching for each of the reference in column I to match the ones in column "B".

As you can see he finds I5 (Luna0003) but also should find I3 and I6 because they are together in C3.

Sorry if didn't explain correctly before

Really appreciate it.

[AliGW]

In the workbook, please show what you want to see, NOT formulae that do not give the results you want.

[Chevaleridulys]

Okay.

I've updated the file.

Thank you
PSG

[JeteMc]

Try the following array entered formula** in F3 and down:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[bebo021999]

I believe upper solution from JeteMc doesnot work for the case : Company A and Luna0003
Before start jumping in, I have one question: Is there any other case of "LunaXXXX"? .i,e "NewyearXXX?

[JeteMc]

Here is the file with the array entered formula applied.
The formula identifies the three client/reference number combinations that Chevaleridulys mentions in post #5.

[Phuocam]

Add wildcard "*":

=IF(ISERROR(VLOOKUP("*"&I3&"*",C:C,1,FALSE)),"Non Exist","Exist")

[Chevaleridulys]

Thank you all for your input. It works This is what I wanted. You are a great community

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.