This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have two sheets within my excel file. They are Data and Manifest. The Data sheet contains three columns (File Name, Market and #). The Manifest sheet has multiple columns.
Data
| File Name | Market | # |
|---|---|---|
| Bob1 | ar-sa | 24 |
| Bob2 | ar-sa | 25 |
| Bob3 | de-de | 24 |
| Bob4 | nl-nl | 26 |
| James1 | ar-sa | 25 |
| James2 | de-de | 24 |
| James3 | de-de | 26 |
| James4 | nl-nl | 24 |
| Bob1 | de-de | 25 |
| Bob1 | nl-nl | 26 |
Manifest
| File Name | ar-sa | de-de | nl-nl |
|---|---|---|---|
| Bob1 | |||
| Bob2 | |||
| Bob3 | |||
| Bob4 | |||
| James1 | |||
| James2 | |||
| James3 | |||
| James4 |
What I am trying to do is to fill in the Manifest sheet for each row and column header with the corresponding number from the Data sheet.
I have tried using Index with Match, but I am missing something.
=INDEX(Data!$C:$C,MATCH(A$2,Data!$A:$A,0),MATCH($B1,Data!$B:$B,0))
Is there something I'm missing? Is there an easier way to solve this problem?
Thank you for your assistance!
-Brian
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
In cell B2 of Manifest sheet, enter this formula and copy down/right
=IFERROR(INDEX(Data!$C$2:$C$11,MATCH(1,INDEX((Data!$A$2:$A$11=$A2)*(Data!$B$2:$B$11=B$1),,),0),1),"")
Hope this helps.
Answer accepted by question author
Hello Brian,
You could use a Pivot table to return the values in Manifest.
However, if you want a formula then assuming the values are in the cells as per the pictures below then the formula in cell B2 of Manifest sheet as per the following and copy to the remaining cells of the table in Manifest.
=SUMIFS(Data!$C:$C,Data!$A:$A,$A2,Data!$B:$B,B$1)
Data Sheet:
Manifest Sheet:
Many thanks to both for the quick replies! This is immensely helpful.
Thank you for your quick reply!
I have one wrinkle which I'm wondering if you could help me with. Is there a way to pull the values from the Manifest table if they are strings rather than numbers?
Thank you!