CODEWITHSUNDEEP
excel
Apolo
Apolo

Reputation: 4050

VLOOKUP with multiple value key

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

Answers (1)

Robin Gertenbach
Robin Gertenbach

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

Related Questions