INDEX MATCH Help!

[dinakar]

I have 2 sheets of data in a workbook as below:

Sheet source:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Category1[/TD]
[TD]Category2[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]sports[/TD]
[TD]apparel[/TD]
[TD]9[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]sports[/TD]
[TD]accessories[/TD]
[TD]6[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]sports[/TD]
[TD]other[/TD]
[TD]23[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]casual[/TD]
[TD]apparel[/TD]
[TD]15[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]casual[/TD]
[TD]accessories[/TD]
[TD]20[/TD]
[TD]17[/TD]
[/TR]
</tbody>[/TABLE]

Sheet Destination:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Category1[/TD]
[TD]Category2[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]sports[/TD]
[TD]apparel[/TD]
[TD]FORMULA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sports[/TD]
[TD]accessories[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sports[/TD]
[TD]other[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]casual[/TD]
[TD]apparel[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]casual[/TD]
[TD]accessories[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

'Sheet Destination' is formatted differently, and I need to pull values from 'Sheet source' though its similar.

I used the formula: =(INDEX(Destination!$C$2:$C$89,MATCH(Source!$A27:$A36&$B27:$B36,Destination!$A$2:$A$89&Destination!$B$2:$B$89,0)))

But its returning me Error value: #VALUE !

Please help!

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 500"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Category1[/TD]
[TD]Category2[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]sports[/TD]
[TD]apparel[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]sports[/TD]
[TD]accessories[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]sports[/TD]
[TD]other[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]casual[/TD]
[TD]apparel[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]casual[/TD]
[TD]accessories[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]17[/TD]
[/TR]
</tbody>[/TABLE]

destination​

Copy formula down and across
[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=INDEX(souce!C$2:C$6,MATCH(destination!$A2&destination!$B2,souce!$A$2:$A$6&souce!$B$2:$B$6,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@dinakar

What is the reason for copying source to destination by means of a formula as the action creates an exact copy?

 

[dinakar]

Thank you @Scott T -Unfortunately, it didn't work. I tried evaluating the formula and it seems that something near the MATCH is not a '#VALUE!'

 

[dinakar]

@Aladin Akyurek - That's true, there is a company specific format the table needs to be in. I am gathering the source data from Access database through VBA , and it is not formatted.

 

[Aladin Akyurek]

@Aladin Akyurek - That's true, there is a company specific format the table needs to be in. I am gathering the source data from Access database through VBA , and it is not formatted.

What does "not formatted" mean? The source (from an Access database) consists of 4 columns and both source and destination look similar as far as we can judge from what you have posted.

 

[Scott T]

Did you use Control+Shift+Enter?

 

[dinakar]

there are few other rules, where some items are rolled up and in the destination table, I have few formulas to do it. And few other categories are merged. I have just posted an example of what I need. the actual table is different.

 

[Aladin Akyurek]

source

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td] Category1[/td][td] Category2[/td][td] July[/td][td] August[/td][/tr]
[tr][td]

2​

[/td][td] sports[/td][td] apparel[/td][td] 9[/td][td] 11[/td][/tr]
[tr][td]

3​

[/td][td] sports[/td][td] accessories[/td][td] 6[/td][td] 14[/td][/tr]
[tr][td]

4​

[/td][td] sports[/td][td] other[/td][td] 23[/td][td] 15[/td][/tr]
[tr][td]

5​

[/td][td] casual[/td][td] apparel[/td][td] 15[/td][td] 19[/td][/tr]
[tr][td]

6​

[/td][td] casual[/td][td] accessories[/td][td] 20[/td][td] 17[/td][/tr]
[/table]

destination

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td] Category1[/td][td] Category2[/td][td] July[/td][td] August[/td][/tr]
[tr][td]

2​

[/td][td] sports[/td][td] apparel[/td][td] 9[/td][td] 11[/td][/tr]
[tr][td]

3​

[/td][td] sports[/td][td] accessories[/td][td] 6[/td][td] 14[/td][/tr]
[tr][td]

4​

[/td][td] sports[/td][td] other[/td][td] 23[/td][td] 15[/td][/tr]
[tr][td]

5​

[/td][td] casual[/td][td] apparel[/td][td] 15[/td][td] 19[/td][/tr]
[tr][td]

6​

[/td][td] casual[/td][td] accessories[/td][td] 20[/td][td] 17[/td][/tr]
[/table]

In C2 of destination control+shift+enter, not just enter, copy across, and down:

=INDEX(source!$C$2:$D$6,MATCH($B2,IF(source!$A$2:$A$6=$A2,source!$B$2:$B$6),0),MATCH(C$1,source!$C$1:$D$1,0))


Note. Control+shift+enter >> Press down the control and the shift keys at the same time while you hit the enter key. If done as intended, Excel itself puts a pair of { and } around the formula in recognition.

 

[dinakar]

Thank you @Scott T and @Aladin