Lookup on Mult Match

**day92** · 08-27-2012, 02:12 PM

Hi Guys - I'm trying to fix someone's mess in the office and got stuck. Now its my mess! What I'm trying to do is lookup values based on ID and DX number. I used a combination of index/match but I'm not getting the desired results.

In the attached sheet on the Analysis tab im trying to fill in the values from column E to column L. The lookup criteria is based on the ID and the DX number. The problem im running into is that the DX number is in multiple columns in the data tab - the DX number is in column M-P on the data tab. I want to return the value is there is a match on the ID # and the DX in any of the those columns. The formula works for the first couple of the rows but in the 3rd row it is not working. There is a match on ID#10000 and DX number 272.4 in the data tab.

Any thoughts?

**NBVC** · 08-27-2012, 02:26 PM

Try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**tigeravatar** · 08-27-2012, 02:33 PM

day92,

In 'Analysis' sheet cell G2 is this array formula copied over and down:

Please Login or Register  to view this content.

[EDIT]
Looks like I got beat to it by NBVC. Nice use of MMULT btw, good way to avoid an array formula in this case. Didn't even occur to me

**day92** · 08-27-2012, 02:43 PM

NBVC - you never sees to amaze! Couple of questions -

- Ive never seen the MMULT formula, how does that work?
- why do you put the 1 after the match function?

TA - thanks for your help too!

**NBVC** · 08-27-2012, 03:22 PM

It's really hard for me to explain....

As per the definition of the function MMULT by MS in Excel help:

Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.

So Array1 is 4 columns by 75 rows (your table) and Array2 is 1 column by 4 rows (the virtual table {1;1;1;1}) to get a result of 75 rows by 1 column... each of these rows is the result of testing each element of each row against criteria M2. So a a match is considered when any of these row results has a value greater than 0... since the -- converts TRUE/FALSE to 0/1... coincidentally the formula as it stands will only work if there is one exact match of Dx number in the 4 columns... the better formula might be:

Please Login or Register  to view this content.

The Match(1,... then checks for the first 1 in the array produced by multiplying the 2 criteria (Data!$A$2:$A$75=$A2) and (MMULT(--(Data!$M$2:$P$75=$M2),{1;1;1;1})>0) together (remember that TRUE*TRUE=1 and all other combos = 0).

I suggest you change the 75's to 7's temporarily, and use the formula Evaluation tool to better see the concept in action without too much elements...