Finding values from a not joined table

[MrBosch]

Hi All

I have read a lot about joining tables but perhaps there is an easier way. I have two sources:

An employee name (from Sheet1) can reside as a value in Sheet 2 source as a 'supporter' or a 'backup supporter' for a product.

If I select a Product from 'Sheet 2' and the name of the employee is mentioned as 'Support 1' it should match the name with the 'Employee nema' in Sheet 1 and retrieve the Phone number and E-mail. Can this be done without joining tables?

So if I select BIKES from the Product list:
In case you need help for bikes, first contact John Ehring on 12148765400 or via john@doe.com 
Else contact Sasha Tott on 12148765240 or via sasha@doe.com

Thanks

[marcus_sommer · Accepted solution]

Matching data between not associated tables is in general possible but usually only for specific types of control and navigation and not to display them in any way. The last could become more complex and tedious as helpful ...

Nevertheless you may apply something like:

only({< [Employee name] = p([Support 1])>} [E-mail])

to fetch the mail.

IMO better would be to develop a regular data-model and associating all relevant data. In your case the support-employees from the sheet2 might be transformed per crosstable or just loaded twice to move them into a single field which is then linkable to the sheet1. Meant is something like:

sheet2:
load Product, [Support 1] as [Employee name], 'Support 1' as Type from sheet2;
concatenate(sheet2) 
load Product, [Backup Support] as [Employee name], 'Backup Support' as Type from sheet2;

- Marcus

[MrBosch]

Hello Marcus!

Thank you very much for the different solutions provided.
I have learned a lot. Got it working like a charm with the ONLY function and will also try the better solution in the LOAD-part.

Great support!
Best regards,
Alex