Formula to lookup values from two tables and combine to a third table IF value is column is not empty and duplicate

[priisha]

Hi,

I have two PowerPivot table that I want to combine and copy information to a third table, however the search criteria is empty as the two PowerPivot tables' amount of data changes from month to month.

1) I want the formula in column K for table 3 to be able to copy values from column B (VAT no) IF the Customer no (column A) starts with K
2) After the formula has copied all values from table 1 in column VAT no, I want it to start looking for values in column G, however if it's a duplicate of a value in column B (VAT no) then it should be skipped.

If I only had one table my formula in column K would be =IF(LEFT(A3;1)="K";B3;"").
Please note that a formula is what I'm looking for and not a VBA code and only for VAT no in table 3, the ones for Sum Goods and Sum Services are all figured out.


Many thanks in advance for the support!

Table 1 goods
Customer no	VAT no	SUM
K00452	DK20286547	-7 404,00​
K01720	DK39087502	-4 000,00​
K02683	NL08114211B01	-90 000,00​
Total sum		-7 404,00​

Table 2 services
Customer no	VAT no	SUM
K01720	DK39087502	-86 240,00​
K08790	SE55681985101	-89 200,00​
Total sum		-7 404,00​

The result for table 3 should be something like this

Table 3 RESULT
VAT no	Sum Goods	Sum Services
DK20286547	7 404,00​
DK39087502	4 000,00​	86 240,00​
NL08114211B01	90 000,00​
SE55681985101		89 200,00​

 

[rEdGeAr]

Hey!
you can try INDEX and MATCH
as;L13= INDEX(C3:C5,MATCH(K13,A3:A5,0))
similarly, M13= INDEX(H3;H4,MATCH(K13,F3:F4,0))
Hope this helps

 

[priisha]

Hey!
you can try INDEX and MATCH
as;L13= INDEX(C3:C5,MATCH(K13,A3:A5,0))
similarly, M13= INDEX(H3;H4,MATCH(K13,F3:F4,0))
Hope this helps

Hi,
thanks for your reply, but what I'm looking for is for a formula that finds the VAT no from the two tables 1 and 2 as these values changes from month to month and I need a solution where I don't have to check that there are VAT nos that are duplicates.

Given your INDEX and MATCH formula, I need to have K3 as fixed values meaning that I know exactly what I'm looking for.
As shown in the picture, the table 3 is all empty when table 1 and 2 are empty but when there are values in table 1 and 2, table will need to first look for VAT numbers in table 1 and show it in table 3, and when it reaches an empty row the formula will start looking for values in table 2's VAT no column and exclude duplicate values that already exist in table 1.
For values in column L and M as I also mentioned in the description I already have a formula that can look for information from needed.

Hope I explained myself better this time.