Multiple Criteria Index Match - #N/A!

[djtoney]

I read many posts and researched the Index Match function & still keep getting #N/A

I have 2 sets of data that I need to compare for a data conversion project. I need to update the old dataset with new account, subledger, department values in order to get the closing balances to match and determine if the conversion is on the right track. Here is my setup:

O P Q Z AA AB AC AD
Legacy Acct SubAcct LegDept Account Subledger dept OU Balance
33004 4501 099 306001 01201 ABC 4500
10005 0 501 100100 000ABC000 020101 DEF 1000
40601 5000 206 100320 000DEF000 036459 XYZ 1500


I am getting values for the new account, dept, OU & balance using VLOOKUP functions. However the subledger needs to be based on a combination of the account and subaccount from the legacy data. The lookup table is one called Mapping (which is a named range.) It is set up as such:

A B C D E F
LegacyAcct LegacySubAcct Desc New Acct New Desc New SubLedger
10395 0 a/p 306001 a/p tax 0
20310 5001 loan 340850 loan 000ABC000
30230 0 cash 356020 cash 000DEF000


The issue is I need to find out what is causing the #n/a.

I tried this:
=index(Mapping,MATCH(1,(a:A=o2)*(b:b=q2),0),6)
Which give me the lovely brackets! {}!!
and this:
=INDEX(mapping,MATCH($02&q2,$A$2:$A$303&$B$2:$B$303,0))

I also verified
The source data and the destination table are formatted "number/general"
There are no dashes or spaces in the data

What am I missing?

Thanks in advance for any help you can provide! ♥

 

[Chrisdontm]

Re: Multiple Criteria Index Match - #N/A! (ugh!!)

From what I can tell,

{Since this is working with multiple ranges it is an array formula.
So after putting it into the cell, you have to hold the Ctrl. + Shft, and hit Enter. to make it work.}

It didn't work at first because there was no matching data.
So I had to change the data to test it.
Then it gave me the data from one row off.
So I had to insert a minus one after the MATCH formula which was giving the row number.
(It counted what row it was on but when counting it in the table it skipped the heading row.)
Then I added in an IFERROR part to the formula in case there is no correct answer.
So try this;

=IFERROR(INDEX(Mapping,MATCH(1,(A:A=O2)*(B:B=Q2),0)-1,6),"")
__________________________________

The second formula needed a reference to which column you were indexing.
It too needs to be entered in as an array.

=IFERROR(INDEX(Mapping,MATCH(O2&Q2,$A$2:$A$303&$B$2:$B$303,0),6),"")