0

I'm trying to get Excel to fill in a table with values based on the Headings of its Rows & Columns. The source data is in a list in another table.

Excel

Basically I want to get the price of the products from TAB 2 into the first table.

0

2 Answers 2

0

Easier would be a sumproduct in B2

=SUMPRODUCT(Sheet2!$C$1:$C$8*(Sheet2!$A$1:$A$8=$A2)*(Sheet2!$B$1:$B$8=B$1))

Drag it across your entire table.

0

Assuming you first Tab is called Tab1 and second tab is called Tab2 and your tabs start at A1.

You can easily do this with INDEX-MATCH:

In tab1 B2 enter the following

INDEX($C$1:$C$8,MATCH($A2&B$1,'Tab2'!$A$1:$A$8&'Tab2'!$B$1:$B$8,0))

After entering this press Ctrl+Shift+Enter to enter it as array formula. Afterwards you can drag the formula to the rest of tab1.

9
  • It doesn't seem to be working, the last semicolon is preventing me from entering the formula
    – Bahtfeng
    Oct 12, 2016 at 10:11
  • @Bahtfeng either use the comma or the semicolon, depending on your regional settings, not both. It's just a typo Oct 12, 2016 at 11:14
  • No need to CSE - index is already an array formula. Oct 12, 2016 at 11:15
  • @Raystafarian I tried not pressing CSE, but then I did not get the desired results.
    – Michthan
    Oct 12, 2016 at 11:55
  • 1
    @Michthan I've fixed it :) Wasn't a tab name issue, but I was missing a number in the range for the match - Silly mistake. Thanks for the help!
    – Bahtfeng
    Oct 14, 2016 at 5:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .