VLOOKUP and merge sheets to one file

Question

I have three Excel sheets, with some similar column headers:

sheet1:
Id | tax | amount
 34       23%     2300

sheet2:
Id | rate | cost
 13       63%      8300

sheet3:
Id | rate | cost | balance
 34       23%         2300          120

How can I merge them by ID to get one complete Excel sheet?:

final combined sheet:
Id | rate | cost | balance | tax | amount |
 13       63%         8300                                   
 34       23%         2300             120            23%             2300

I've tried searching for solutions but I need a simple and explanatory answer please.

Answer 1

What you want to use is VLOOKUP. Basically, set up all your columns in another sheet. Identify all unique ids. You can do this by copy-pasting all IDs one after another and then using Data > Eliminate Duplicates. After this, you can use VLOOKUP to retrieve all the columns from the proper worksheets.

VLOOKUP has the following usage: =VLOOKUP(unique_value, lookup_range, column_no_to_retrieve, 0)

The last parameter is 0 for exact match or 1 for approximation match. The function will lookup on the first column of "lookup_range" and will return the cell that is in the column specified by "column_no_to_retrieve" on the same line of the first match. If there are more than one match, the others are ignored.

Answer 2

The desired result is possible without VLOOKUP or any equivalent formula:

On sheet1 insert three columns between Id and tax.
Copy sheet1, sheet2 and sheet3 into a fourth sheet, copy headers to complete row1 and delete headers in other rows.
Sort on Id.
Subtotal five data columns with SUM for each change in Id.
Copy sheet and Paste Special, Values over the top.
Filter on Id for Does not contain 'tot' and delete all rows numbered in blue.
Delete Grand Total row.
Select Id column and Replace ' Total' with nothing.
Subtotal with Remove All.
Format to suit.

Answer 3

if you have all ids ready without duplicates, please follow this steps.

1) paste all the ids in A2 of sheet 4.

Ex: A2 - 34 A3 - 13

2) place the below formula in cell B2 in sheet 4 and select B2, C2, .... up to the ids numbers of column F for ex your total ids is 10 then Select B2, C2....F10 and press Ctrl+D

=IFERROR(INDEX(Sheet1!$A:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0)),IFERROR(INDEX(Sheet2!$A:$E,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)),IFERROR(INDEX(Sheet3!$A:$E,MATCH($A2,Sheet3!$A:$A,0),MATCH(B$1,Sheet3!$1:$1,0)),"")))

Answer 4

You can try this provided that each sheet have unique ID's only (no duplicate).
Kinda long formula though and not Vlookup.

=IFERROR(INDEX(Sheet1!$A:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0)), IFERROR(INDEX(Sheet2!$A:$E,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)), IFERROR(INDEX(Sheet3!$A:$E,MATCH($A2,Sheet3!$A:$A,0),MATCH(B$1,Sheet3!$1:$1,0)),"")))

Basically it is a simple Index-Match combination with nested Iferror to try searching on different sheets.
So if you have your sheets set up like below:

Enter formula in Cell B2 in the final sheet to get your desired output.
Copy and paste formula to other cells.

Haven't tested on huge data set, you never mentioned it in your question anyway.
But, I think calculation time is manageable for a few thousand rows.
Also, the range address I used are entire columns, so it still can be improved by explicitly defining the ranges you want to work on by using Named Ranges set-up dynamically.

Answer 5

For Id's simply link any sheet that has more id to the final sheet and for rest of the data us this:

=IF(OR(B$1="ID",B$1="TAX",B$1="AMOUNT"),SUMPRODUCT((Sheet1!$A$1:$C$1=B$1)*(Sheet1!$A$2:$A$10=$A2)*(Sheet1!$A$2:$C$10)),SUMPRODUCT((Sheet3!$A$1:$D$1=B$1)*(Sheet3!$A$2:$A$10=$A2)*(Sheet3!$A$2:$D$10)))

If you set your all four sheets as Tables, there will be no need to update the cell references. The formula will look into the left most column of the final sheet, where data is present via a link, and the result will be used to fetch data from rest of sheets.

Hope that helps.