How to find result from two tables

[Merrysa]

Hi All,
I have two attached problem 1. where I have to find the result from two tables., 2. attached is the sheet.
could you please guide me how to solve it.

Thank you

[Olly]

Your worksheet is confusing.

Which 'tables' are your actual source data, which is your required outcome?

[Merrysa]

Ho Olly,
There are two problem,

1) I need result in F11 cell, Tables to be compare A to C column and E to F column.
2) ) I need result in K17 - M23 cells, Tables to be compare J to L columns

[José Augusto]

Hi

Use, for solve the first problem, in F11

Please Login or Register  to view this content.

[Olly]

The data in J1:L12 - do you need to perform currency conversion on these values, too?

[José Augusto]

To solve the second problem
use in K17 and copy down and forward

Please Login or Register  to view this content.

[alansidman]

for your first issue, you can use Power Query to merge the two tables for the expected results. Here is the Mcode for that solution.

Please Login or Register  to view this content.

[Olly]

Blending data from multiple tables, to generate flexible outputs is a perfect example of when to use PowerPivot.

In this answer, I've assumed you wish to apply currency conversion for the profit values in J:L.

Format your three source data tables as Tables:

Range A1:C6 = tblCountries
Range E1:F4 = tblFX
Range J1:L12 = tblProfits

Use PowerQuery to prepare each table, and Load To Data Model:

Query Countries:

Please Login or Register  to view this content.

Query FX:

Please Login or Register  to view this content.

Query Profits:

Please Login or Register  to view this content.

With those three tables loaded to the Data Model, create relationships:
FX[Currency] - Countries[Currency]
Countries[Country] - Profits[Country]

Capture.PNG


Add a couple of MEASURES, to give the results you want:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Finally, use Pivot Tables to present your results.

Note that as IND and SL do not appear in your first currency table, we can't identify which currency to use, so no value is returned in the 'Profit USD' measure, for these countries. To fix this, add those contries into the tblCountries table with their corresponding currency code, and ensure those currencies are listed in tblFX.

Attached workbook demonstrates all the above.

[Merrysa]

No Olly, in this case I don't require currency conversion. I need only output based on J1:L12.

[Olly]

Then just use a standard pivot table.

[Merrysa]

@José Augusto

Thank you Jose, It works, Have a query "What exact ,1 does in If error function end"

=VLOOKUP($E11,$A$2:$C$6,3,FALSE)*IFERROR(VLOOKUP(VLOOKUP($E11,$A$2:$C$6,2,FALSE),$E$2:$F$5,2,FALSE),1)

[José Augusto]

Hi @Merrysa

That value 1 is the conversion from USD to USD., i.e. to avoid IFERROR use
E5="USD"
F5=1

[Merrysa]

Got it, thanks a lot

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Merrysa]

@Olly
Very impressive, actually I never worked on PowerPivot , Understanding your method

[Merrysa]

Very impressive, actually I never worked on PowerPivot , Understanding your method

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.