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.


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


2 Answers 2


Easier would be a sumproduct in B2


Drag it across your entire table.


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


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

  • 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 .