INDEX MATCH Help!

dinakar

New Member
Joined
Nov 8, 2017
Messages
11
I have 2 sheets of data in a workbook as below:

Sheet source:
Category1Category2JulyAugust
sportsapparel911
sportsaccessories614
sportsother2315
casualapparel1519
casualaccessories2017

<tbody>
</tbody>

Sheet Destination:
Category1Category2JulyAugust
sportsapparelFORMULA
sportsaccessories
sportsother
casualapparel
casualaccessories

<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

Well-known Member
Joined
Dec 14, 2016
Messages
2,586
Office Version
365, 2016
Platform
Windows
Excel 2010
ABCD
1Category1Category2JulyAugust
2sportsapparel911
3sportsaccessories614
4sportsother2315
5casualapparel1519
6casualaccessories2017

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

Copy formula down and across
Array Formulas
CellFormula
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>
 

dinakar

New Member
Joined
Nov 8, 2017
Messages
11
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
Joined
Nov 8, 2017
Messages
11
@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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
@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
Joined
Dec 14, 2016
Messages
2,586
Office Version
365, 2016
Platform
Windows
Did you use Control+Shift+Enter?
 

dinakar

New Member
Joined
Nov 8, 2017
Messages
11
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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.
 

Forum statistics

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

Some videos you may like

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