I would be really appreciative if the forum experts help me to come up with the formula to:

1. Lookup each Product ID in column A in "Lookup" tab. Note that the Product IDs are duplicates.
2. Return the "Product Name" for corresponding Product ID from column B ("Unique IDs" tab) to column B in "Lookup" tab.
3. Return the "Category Hierarchy" for corresponding Product ID from column C ("Unique IDs" tab) to column C in "Lookup" tab.

I was trying to use INDEX and MATCH functions but can't make it work. Please see attached sample data file.

=INDEX('Unique IDs'!$A$2:$C$117,MATCH(A2,'Unique IDs'!B:B),0)

Sample Data - Product IDs Match up.xlsx

Thank you so much!