CODEWITHSUNDEEP
excelexcel-formula
Axa
Axa

Reputation: 47

Use Index and Match with multiple lookup values to get a single result

Using INDEX & MATCH, I'm trying to copy some data across from one worksheet to another (both of which are in the same workbook). I want to grab the description of an item via its ID and it's subset lettering.

EXAMPLE:

Sheet 1 (Destination)

             Formula goes here
                     ↓
   +------+---+---------------
   |  A   | B |      C
---+------+---+---------------
 1 | R976 | A | Lazy Brown Dog
 2 | R976 | F | Grey Bird
 3 | R976 | D | Fox

Sheet 2 (Source)

   |  A   |    B   | C |      D
---+------+--------+---+---------------
 1 |  ID  | Subset |   | Description
---+------+--------+---+---------------
 2 | R976 |    A   |   | Lazy Brown Dog
 3 | R976 |    D   |   | Fox
 4 | R976 |    F   |   | Grey Bird

I want the formula in column C of Sheet 1 to grab the description from column D of Sheet 2, based off the ID in column A and the unique letter in column B.

Current:

=INDEX(A1:A4,MATCH(A1,Sheet2!A2:C4, 0))

What I'm trying to do:

=INDEX(A1:A4,MATCH(A1&B1,Sheet2!A2:C4, 0))

However, I'm getting an #NA even after using CTRL+SHIFT+ENTER.

Upvotes: 1

Views: 170

Answers (1)

YowE3K
YowE3K

Reputation: 23994

I believe you want Sheet1!C2 to have a formula of:

{=INDEX(Sheet2!$D$1:$D$4,MATCH(Sheet1!$A2&Sheet1!$B2,Sheet2!$A$1:$A$4&Sheet2!$B$1:$B$4,0))}

Then copy that down to cells C3 and C4.

Upvotes: 1

Related Questions