excel function question

Copper Contributor

1. I have a list of codes and would like to generate a list of codes that are in Column A from Sheet 1 and not in column A of Sheet2 and return the value in column B from  Sheet 1 of the codes that populate sheet3

 

2. If that is not possible I am looking for a way to generate a list of codes in column a of sheet one if a specific word is found in column b of sheet 1.  I would search by the name of the medication. The medication can be listed with an order sentence or without. 

 

Data set example

 

12345550 ML argatroban 1 MG/ML Injection
122345125 ML argatroban 1 MG/ML Injection
1223455250 ML argatroban 1 MG/ML Injection
1223argtroban
2 Replies
Please provide a specific example of the data and result you want.

@kmb06002 , that could be done with Power Query. 3 steps

- query first table

- query second table

- merge first table with second one with Left Anti and land the resulting table with values from first table for which codes are not in second one.

image.png