How to lookup values and get results in specific formats?

I have a workbook with a tabular data (file attached).

Now, I'm trying to lookup values in a different workbook using the table shown in the attached file. I only need the columns "RefNo" & "EntryNo" where the lookup value will find a match with column "RefNo" and extract only the text portion from column "EntryNo" & according to that show the result.

Basically, 1) if the "EntryNo" value have either of the texts "WAMCP" or "WSILCP" or "WJCP" then the result should show as "CAPITAL", 2) if the "EntryNo" value have either of the texts "WAMOP" or "WSILOP" or "WJOP" then the result should show as "CLINIC", 3) if the "EntryNo" value have either of the texts "WAMPRP" or "WSILPRP" or "WJPR" then the result should show as "PROCEDURE", 3) if the "EntryNo" value have either of the texts "WAMPP" or "WSILPP" or "WJPI" then the result should show as "PHARMA" etc. and no match is found then it should be the text "Not Found".

I've tried the below code but it is long and doesn't work

Please Login or Register  to view this content.

Can anyone help?

I am sure the gods of this forum can make a shorter one but this seems to work

Please Login or Register  to view this content.

Thanks for replying. I need to do some sort of lookup as the workbook where I want the result is different than where the table is. The workbook where I want the result looks like
Untitled.jpg

Originally Posted by kvsrinivasamurthy

Attachment not opening. Upload file like post#1.

Just see the image in post #3.

Ok, can you explain your formula and how to use it in the use case mentioned in post#6 ?

Thanks in advance

Here you go.

Possible solution.

Formula:

Please Login or Register  to view this content.

I've added the expected result's in Column C (Result.xlsx). Basically, a vlookup or something like that should use the data in Column B(Result.xlsx) as lookup value then use the table in DATABASE.xlsx as table array, get the values from the column "EntryNo" (DATABASE.xlsx) and convert them using the logic(mentioned below) and put then in Column C (Result.xlsx) as the desired result

Basically, 1) if the "EntryNo" value have either of the texts "WAMCP" or "WSILCP" or "WJCP" then the result should show as "CAPITAL", 2) if the "EntryNo" value have either of the texts "WAMOP" or "WSILOP" or "WJOP" then the result should show as "CLINIC", 3) if the "EntryNo" value have either of the texts "WAMPRP" or "WSILPRP" or "WJPR" then the result should show as "PROCEDURE", 3) if the "EntryNo" value have either of the texts "WAMPP" or "WSILPP" or "WJPI" then the result should show as "PHARMA" etc. and no match is found then it should be the text "Not Found".

I hope I've made it clear enough.

I've downloaded your files. It's not working as desired. All results return the value "CAPITAL" in the column D. Did it work for you ? Then why is it not working for me ?

S04/23-24/11 Matching value WJCP0022 Matching value is CAPITAL (post#1)
You have shown as PHARMA.

You are right there, sorry for that.

But for ROW 6 i.e. SOA588357509144 matching value WAMOP0103, so the correct result should be CLINIC like I mentioned before.

2) if the "EntryNo" value have either of the texts "WAMOP" or "WSILOP" or "WJOP" then the result should show as "CLINIC",

I'm once again adding the files with slightly changed Result.xlsx file for covering various type of "Category" as results.

It is working! Thanks

One last thing, do you mind explaining your formula for me to better understand it ?