Lookup value when 2 conditions meet

[adityavighne]

Hi,

I have two table:

Table 1 contains (ID, Email) and Table 2 contains (ID, Email, Usage)

I want lookup value Usage in Table 1 when two conditionas are meet: ID and Email

below is sample dummy data.

Table 1

ID	Email
1	abc.com
2	fjfgo.com
3	skfj.in
4	kfj.pp
5	sdfb.com
1	sfkh.in
2	abc.com
3	fjfgo.com
4	skfj.in
5	kfj.pp
1	sdfb.com
2	sfkh.in
3	abc.com
4	fjfgo.com
5	skfj.in
1	kfj.pp
2	sdfb.com
3	sfkh.in
4	fjfgo.com
5	skfj.in

Table 2

ID	Email	Usage
1	abc.com	y
2	fjfgo.com	y
3	skfj.in	y
4	kfj.pp	y
5	sdfb.com	n
1	sfkh.in	n
2	abc.com	n
3	fjfgo.com	n
4	skfj.in	n
5	kfj.pp	n
1	sdfb.com	n
2	sfkh.in	n
3	abc.com	n
4	fjfgo.com	y
5	skfj.in	y
1	kfj.pp	y
2	sdfb.com	y
3	sfkh.in	n
4	fjfgo.com	n
5	skfj.in	y

[Tahreem24 · Accepted solution]

@adityavighne  As you have same ID for different email ID so you'll need to create a Key column in both the tables.

Step 1: Create Key column (combining ID and Email) in Table 1.

Key = DATA1[Email]&DATA1[ID]

Step 2: Create the Key Column in Table 2:

Key  = DATA2[Email]&DATA2[ID]

Step 3: Create Bridge Table to join these Key columns:

Step 4: Then Create calculated column to find relevant usage.

Lookup = IF(DATA2[Key]=RELATED(Bridge[Key]),DATA2[Usage],"-")

Output:

[CBO]

Hi,

I guess you could consider the brute force of creating a new hybrid key in each table by concatenating the ID and Email fields and then narrowing back the problem to a lookup on a single key with LOOKUPVALUE.

Curious to see if there's something more elegant.

Best

CBO