Finding LastName and Firstname in a column and returningl specific cells values

[lipfon]

Hello, I am posting on this forum to try to find some help with a VBA script that can do the following:

In one worksheet, I have a first Tab (names) where Column A = Lastname, Column B = FirstName C=Email Address

I have another Tab where column "I" contains text that may or may not contain lastname and firstname.
If I find the combination LastName and FirstName in this column "I", it could be in different order, some letters might
be upper or lower case. Of course if one of the 2 is misspelled, I do not expect to find the combination.

In this second Tab, I have as well one column "P" (payment) with a value like 20,00.

What I would like to do is the following:

In Tab names, column "D", have a function that is looking in Tab2-Column I if FirstName AND LastName are found.

If the combination is not found, display "Non OK; the email adress from column "C""
If the combination is found, display "the row number of column "P" and the value of column "P" , row number = row where the
combination is found

I would like the script to search in the column "I" as I will add rows on a weekly basis

I hope this is clear, if you have question, please just ask.
Many thanks to the ones that will look at this

Philippe / Belgium

Example

LastName FirstName
Doe John
Dae Johny
Die Johnny

"I" column examples:
text te textt teet John Doe dfet ud (row 34)
text te textt teet dae john dfet ud (row 40)
text te textt teet Jon Die dfet ud (row 49

Column "D"

For John Doe: Row 34; 20,00
For John Dae: Row 40; 40,00
For John Die: Non OK, emailaddress@google.com

[ragulduy]

formula solution:
=IF(SUMPRODUCT(IF(ISERROR(FIND(A1,I$1:I$10000,1)),0,1)*IF(ISERROR(FIND(B1,I$1:I$10000,1)),0,1))=1,"Row = " & SUMPRODUCT(IF(ISERROR(FIND(A1,I$1:I$10000,1)),0,ROW(I$1:I$10000))*IF(ISERROR(FIND(B1,I$1:I$10000,1)),0,1)) & ", Value = " & INDEX(P$1:P$1000,SUMPRODUCT(IF(ISERROR(FIND(A1,I$1:I$10000,1)),0,ROW(I$1:I$10000))*IF(ISERROR(FIND(B1,I$1:I$10000,1)),0,1))),"Non OK; " & C1)
In D1 and copy down.

It's an array formula so you need to confirm with ctrl+shift+enter
I didn't see what your other tab was called so I've based the formula on everything being on the same sheet. You will need to add sheet references as neccessary.

[lipfon]

Hi many thanks for this, I will try and let you know.
Ideally, the names should be in one sheet, the text to find in another one, but I can have all data in the same one. What matters is that it works :-)
I will let you know. Thanks