INDEX MATCH Help!

[dinakar]

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

Sheet source:

Category1	Category2	July	August
sports	apparel	9	11
sports	accessories	6	14
sports	other	23	15
casual	apparel	15	19
casual	accessories	20	17

<tbody>
</tbody>

Sheet Destination:

Category1	Category2	July	August
sports	apparel	FORMULA
sports	accessories
sports	other
casual	apparel
casual	accessories

<tbody>
</tbody>

'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

	A	B	C	D
1	Category1	Category2	July	August
2	sports	apparel	9	11
3	sports	accessories	6	14
4	sports	other	23	15
5	casual	apparel	15	19
6	casual	accessories	20	17

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

destination​

Copy formula down and across

Array Formulas Cell Formula C2 {=INDEX(souce!C$2:C$6,MATCH(destination!$A2&destination!$B2,souce!$A$2:$A$6&souce!$B$2:$B$6,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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

Row\Col	A​	B​	C​	D​
1​	Category1	Category2	July	August
2​	sports	apparel	9	11
3​	sports	accessories	6	14
4​	sports	other	23	15
5​	casual	apparel	15	19
6​	casual	accessories	20	17

destination

Row\Col	A​	B​	C​	D​
1​	Category1	Category2	July	August
2​	sports	apparel	9	11
3​	sports	accessories	6	14
4​	sports	other	23	15
5​	casual	apparel	15	19
6​	casual	accessories	20	17

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