# INDEX MATCH Help!

#### dinakar

##### New Member
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 !

#### Scott T

##### Well-known Member
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
 C2 Cell Formula {=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>

##### MrExcel MVP
@dinakar

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

#### dinakar

##### New Member
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

##### New Member
@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.

##### MrExcel MVP
@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

##### Well-known Member
Did you use Control+Shift+Enter?

#### dinakar

##### New Member
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.

##### MrExcel MVP
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.

1,081,425
Messages
5,358,604
Members
400,505
Latest member
JacquiT

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...