Reputation: 4050
For those with an SQL background, I am trying to do a kind of "JOIN" with multiple criteria.
Here is my worksheet :
TABLE 1
┌──────┬─────────┬─────┐
│DOMAIN│BRAND │VALUE│
├──────┼─────────┼─────┤
│SKI │NIKE │ 42 │
│SKI │GOPRO │ 94 │
│SKI │RIP CURL │ 78 │
│SNOW │NIKE │ 38 │
│SURF │BILLABONG│ 13 │
│SURF │HURLEY │ 99 │
│SNOW │NIKE │ 72 │
└──────┴─────────┴─────┘
TABLE 2
┌──────┬─────────┐
│DOMAIN│BRAND │
├──────┼─────────┤
│SNOW │NIKE │
│SURF │HURLEY │
│SKI │FOO │
└──────┴─────────┘
I want to highlight rows in Table1 that are in Table2 (Here is should highlight rows 4 and 6).
I tried a VLOOKUP but it only takes one key as search argument. I also tried a combination of IF and MATCH, but it would highlight every rows of a brand if its name is in TABLE 2.
Do you have any hints for me please ?
[Edit] Before duplicate: I found this topic: Excel - VLOOKUP with multiple criteria but the answer does not seems to work anymore.
Upvotes: 2
Views: 422
Reputation: 10806
A proper SQL-like join will also highlight row 7.
That you can achieve for example with SUMPRODUCT()
.
Putting the first table with the headers in A1
and the second with the headers in A12
you can do the following:
=SUMPRODUCT(($A$13:$A$15 = A2) * 1,
($B$13:$B$15 = B2) * 1)
This creates an array of TRUE/FALSE whether the values equal the ones in table 2 and multiplies them by 1 converting the booleans to 1/0.
Then it's multiplying the rows of 1/0 values and sums them (can be done with any number of columns).
Assuming the values in table 2 are unique you will get 0 or 1 as a result for every row in Table 1.
These you can then compare to 1 to get a Boolean value again for your conditional formatting.
Upvotes: 1