Index/Match multiple columns in Excel

Question

I have 2 sheets. Sheet 1 is set up similarly to:

Keyword  Domain  Rank
  A        Z      1
  B        Z      2
  C        Z      3
  D        Y      10
  E        Y      15
  B        Y      20

And sheet 2 is set up like:

Keyword  (Domain Z)  (Domain Y)
  A           1          -
  B           2          20
  C           3          -
  D           -          10

I'm trying to have a formula that will compare the keywords in Sheet 2 with those in Sheet 1 and then return the rank that corresponds to the correct domain (that's specified in Sheet 2 for that column). I can't get any formula I use to evaluate. I've used 2 formulas so far:

=INDEX(Raw!$H$11:$H$322, MATCH(A3,IF(Raw!$D$11:$D$322=All!$B$2,Raw!$B$11:$B$322),0))

The above formula works, to a point. The problem is that it simple pulls the domain for the first instance of the keyword found, which doesn't always match the domain in the column of sheet 2. The second formula I've tried:

=INDEX(Raw!$H$11:$H$322, MATCH(B3,MATCH($C$2,Raw!$D$11:$D$322,0)))

Answer 1

To make the values appear in the Sheet 2 table, use the following formula:

=SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=$F$1),$C$2:$C$7)

This returns 0 for non-matches - you can either format the cells to display 0 how you want, or you can use the longer/uglier:

=IF(SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=$G$1),$C$2:$C$7)<>0,SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=$G$1),$C$2:$C$7),"-")

---

To calculate the rank on the first sheet based on the data from the second sheet:

=VLOOKUP(A2,$F$2:$H$5,MATCH(B2,$G$1:$H$1,0)+1,FALSE)

For sample purposes, this just put your sheet2 data in F1:H5.

This looks for the corresponding keyword and then uses match to pick the right column. I named the columns Z and Y, but if you need Domain included that can be done as well. Note that this causes an error since there is no E defined in your second table - is that the case? If so, it can be adjusted to account for no matches as follows (assuming Excel 2007):

=IFERROR(VLOOKUP(A6,$F$2:$H$5,MATCH(B6,$G$1:$H$1,0)+1,FALSE),"Rank Not Found")