Double Match - Mark W or Aladin???

Posted by Russell Hauf on August 28, 2001 12:57 PM

I have one sheet with 2 "codes". I have another sheet with the same 2 codes and the description of what the 2 codes mean. I want to get the description from the second sheet to the first. I've done it in the past (with INDEX and/or MATCH - I think), but I can't remember exactly how and thought that one of the formula masters on this site would know offhand.

Here's what my data looks like:

Sheet1:

Column A; Column B;
B;D;
B;L;
D;1;
D;Z;
S;F;
S;S;
(etc.)

Sheet2:

Column A; Column B; Column C
B;0; "What B-0 means"
B;D; "What B-D means"
...
S;S; "What S-S means"

I can easily throw the descriptions in via code, but would like the formula solution.

Thanks in advance,

Russell

Posted by Mark w. on August 28, 2001 1:11 PM

Russell, I'd recommend that you insert a "new"
column C on Sheet2 -- right before your descriptions.
Next, enter the formula, =A2&B2, into the "new"
C2 cell and Copy down. Finally, on Sheet1 use
the formula, =VLOOKUP($A2&$B2,Sheet2!$C$2:$D$25,2,0),
to return the corresponding description from
Sheet2. Note: adjust the Sheet2 cell range,
(e.g., Sheet2!$C$2:$D$25) to accommodate all of
your descriptions.

Posted by Aladin Akyurek on August 28, 2001 1:55 PM

on Sheet1,

in C1 enter: =IF(SUMPRODUCT(ISNUMBER(MATCH(A1&B1,Sheet2$A$1:$A$6&Sheet2!$B$1:$B$6,0))+0),INDEX(Sheet2!$C$1:$C$6,SUMPRODUCT(MATCH(A1&B1,Sheet2!$A$1:$A$6&Sheet2!$B$1:$B$6,0))),"")

If you don't mind possible #N/A's, use:

=INDEX(Sheet2!$C$1:$C$6,SUMPRODUCT(MATCH(A1&B1,Sheet2!$A$1:$A$6&Sheet2!$B$1:$B$6,0)))

Hope I didn't mess up with the sheet name (I used a single sheet for testing, whence the warning).

Aladin

==============

Posted by Russell Hauf on August 28, 2001 2:42 PM

Thanks guys

Both great ideas - thank you!