Showing adjacent cell value

Hi, all

I have this one table where it will refer sheet "O" to the raw data which is sheet "R". In sheet "O", the first column will be the name of shop, copied from a drop-down list, which consists of several shops' name. Then, the second column (Material) would be in a HLOOKUP formula. My issue is:

1. How can I do reference for the column next to Material in sheet "R" to be shown in sheet O? I know I could not use VLOOKUP solely as VLOOKUP will only search for the most left data in table (column A)
2. How can I make column Material in sheet "R" to be referred automatically? i.e. when the name of shop changes, the material will change thus, the amount will change too.

P/s: hereby I attached the similar Excel file to my actual Excel file. The raw data in sheet "R" is arranged in that particular way and cannot be changed, unfortunately.

Seeking for advices. Thanks for the help!

Thank you so much! It works