How to pick up correct data in multiple rows and columns in a table

We are selling products to over 6 different sales channels, with each product selling over not more than 4 different channels. The cost prices, margins and selling prices are entered in a table (Table 1).

I am trying to pick up the correct selling prices from this table for each sales channel for each product into another table, as highlighted in yellow in Table 2, using formulas.

Any help is very much appreciated. Thank you in advance.

Joseph

Pick up correct data.png

Put in R6 and copied down and cross:

=IFERROR(INDEX($D$6:$N$8,MATCH("Our Selling Price",$B$6:$B$8,0),MATCH(1,INDEX(($Q6=LOOKUP(COLUMN($C$4:$N$4),COLUMN($C$4:$N$4)/($C$4:$N$4<>""),$C$4:$N$4))*(R$5=$C$5:$N$5),,),0)),IFERROR(INDEX($D$10:$N$12,MATCH("Our Selling Price",$B$10:$B$12,0),MATCH(1,INDEX(($Q6=LOOKUP(COLUMN($C$4:$N$4),COLUMN($C$4:$N$4)/($C$4:$N$4<>""),$C$4:$N$4))*(R$5=$C$9:$N$9),,),0)),""))

Thank you alansidman, azum and Bo_Ry for your prompt help! The formulas are a little complicated for me and the steps for PQ are plenty. I am still trying to understand them all!

When I changed the formulas to expand to the whole row, e.g. $C$4:$N$4 to $4:$4 because of the ever expanding products, the formulas don't seem to work any longer. My Selling Price Summary is actually on a separate sheet.

PQ seems like a good solution, but the problem is user is free to add rows at the top of the data table.

Sorry, I did not explain properly. There are other details below the Item and Product rows, and user is free to add new rows there:

Other Details.png

Originally Posted by alansidman

Should not be an issue as you are uploading a Table. When data is added to the table, PQ grabs it and updates when you click on Refresh All two times.

Oh ya, you are right, the PQ is still working correctly even after adding new rows.

I supposed I will just expand the formula from $C$4:$N$4 to $C$4:$ZZ$4. Thanks everyone for your help! Here is a star for you all.