INDEX & MATCH arrat across multiple worksheets on MacBook Pro

[simon_ives]

Hi all, I hope someone can shed some light on why my array isn't executing.

What I'm attempting to do is reconcile a statement of transactions for multiple credit cards across the records within the G/L for the same credit cards. I'm after details on which transactions are within the G/L so I can follow up with the card holders in relation to the absent G/L records.

I'm using the following formula:

=INDEX('210603 June'!A1:K781,MATCH(1,('210603 June'!J2:J781=B2)*('210603 June'!G2:G781=J2),0),1)

And to break it down:

The formula is placed within a cell on the credit card transaction statement worksheet

• INDEX is referencing the data set from the G/L worksheet (210603 June)
• MATCH is searching for an exact match of:
  • '210603 June'!J2:J781 -> The column within the G/L worksheet with the last four digits of the credit card numbers (This data itself is 'cleansed' using the following formula

    =MID(I2, 4, 4)

    as the credit card numbers are included amongst some text content within the same cell.
  • B2 -> The cell within the credit card transaction statement worksheet that has the last four digits of the credit card number to be matched.
  • '210603 June'!G2:G781 -> The column within the G/L worksheet with the transaction amount
  • J2 -> The cell within the credit card transaction statement worksheet that has the transaction amount to be matched
  • 0 -> Looking for an exact match
• 1 -> The column within the G/L worksheet that has the business unit code (will help me identify incorrectly applied codes)

I've completed some simple debugging so far and I'm thinking that there is an issue with executing an array on my MacBook Pro (I usually use excel at work on a Windows PC).

If I select the lookup array

('210603 June'!J2:J781=B2)*('210603 June'!G2:G781=J2)

from the MATCH string and hit "F9" I get a whole heap of zeros and a single 1. If I select the entire MATCH string and hit "F9" I get the resulting line number for the match. Consequently, I'm assuming that the MATCH array is functioning as expected.

If I select the entire formula and hit "F9" I get the expected business unit code. Consequently, I'm assuming that the syntax in the formula is correct.

When I try and execute the formula with the (cmd + return) on the Mac nothing happens. I've tried all manner of keyboard combinations but I can't get this to execute.

So, is the syntax indeed correct and I just can't find the keyboard combination for a MacBook Pro keyboard to execute an array? Or is there something else going on?

 

[Domenic]

It looks like the ranges aren't the same size, and are offset from one another. Maybe ...

=INDEX('210603 June'!A2:K781,MATCH(1,('210603 June'!J2:J781=B2)*('210603 June'!G2:G781=J2),0),1)

...confirmed with CONTROL+SHIFT+ENTER (or Cmd+Return on the Mac). If done correctly, Excel will automatically place curly braces {...} around the formula

Does this help?

 

[simon_ives]

Thanks Domenic but the solution turned out to be quite simple. I didn't have the cell formatted as a number, it was formatted as text. Once I switched the cell over to number format the array executed as expected.

 

[Domenic]

That's great, I'm glad you've sorted it out.

Cheers!