List available stocks

[ameenuksg]

Hi All - I have a product list but not necessarily I have these physical products in my inventory or store.

The product list could go on maybe beyond 500 rows in Sheet 1.

I want to keep up with the stock availability of the products in Sheet 2.

Current(old) and Arrived(New) products could have a thousand rows in the list in Sheet 3.

So all I want to achieve is to create a formula in Sheet 2 to return the products that are available in Sheet 3, Column B after looking up my product list in Sheet 1, Column B, and list them accordingly.

Why I want the formula to lookup the Product List in Sheet 1 because in Sheet 3 there would be other products that are no concern to me.

Note that product ID of the same may be repeated in Sheet 3 but the product list in Sheet 1 is unique.

I would appreciate it if anyone could help me with a solution as I tried using an index match formula and it did not work for me. Thank you in advance.

Column B	Column C	Column B
Product List	Inventory Status	Old and New stocks
Chr12067hf90		Chr12067hf90
ZBr12067hf80		ZBr12067hf80
DH12649gh62		DH12649gh62
Sheet 1	Sheet 2	Sheet 3

 

[ameenuksg]

Hi All - I realize that I could achieve this by using the advance filter ? but If I am going to do this on a weekly basis thought there would be a formula to filter out from Sheet 3. Would appreciate any help

 

[ameenuksg]

I thought I should explain myself here again I want to extract multiple product names from sheet 3 shown above without the advance filter function. I am open to VBA and formula.

 

[ameenuksg]

Hi All I tried index match again(=INDEX(AvailableStocks!$A$2:$A$238,MATCH('List of Parts'!$J3,AvailableStocks!$A$2:$A$238,0),1)) and it seems to work, see below picture.

But I get along list of #N/A, is there a solution in formulas to hide these?