Iferror and vlookup in a formula to combine data from multiple sheets into a one sheet

[tonyan]

I notice that my formula is only check one sheet retrieve the data and stop. It does not check the other sheets and pull that data to added to one sheet. For example, looking up item that is on two sheets but only data from one sheet is inputted on the master sheet. My formula is: =IFERROR(VLOOKUP($A10,'Departure Billed'!$A$6:$J$2640,5,TRUE),IFERROR(VLOOKUP($A10,'Non Signatory Billed'!$A$4:$J$42,5,TRUE),IFERROR(VLOOKUP($A10,'Customs Billed'!$A$6:$J$239,5,TRUE),)))

I have 3 sheets: Departure Billed, Non-Signatory Billed, and Customs Billed. Data my be on all 3 sheets . I need to retrieve the data and sum up to others with the same item name.  Is my formula correct?

[#2]

@tonyan in your formula you have "TRUE" for range lookup parameter which is doing an approximate match, which probably means you are always getting a match to something in the first sheet.  what happens if you use "FALSE" in each case meaning exact match?

[#3]

@tonyan 

Will you share the files?

[#4]

I am not able to share sheet due to company policy. Are you still able to help me?

[#5]

@mtarler   I need the formula to check each sheet even if it finds data in the previous sheet.  add the numbers together.  Is there a formula that will do that?

[#6]

@tonyan sorry didn't catch that sum up part.  You want to use SUMIF, SUMIFS or SUMPRODUCT functions.  Try this:

=SUMIFS('Departure Billed'!$E:$E,'Departure Billed'!$A:$A,$A10) + SUMIFS('Non Signatory Billed'!$E:$E,'Non Signatory Billed'!$A:$A,$A10) + SUMIFS('Customs Billed'!$E:$E,'Customs Billed'!$A:$A,$A10)

I changed the ranges to be the entire column E but change back if you need to restrict that range.

[#7]

@Ilgar_Zarbaliyev see sheet

[#8]

@tonyan  In your sample sheet I used a combination of absolute and relative ranges so you can easily fill down and fill right to fill the whole table.  NOTE that creating a pivot table or using power query may have advantages instead of creating a table of equations.  But for now here is the equation for your sample sheet:

=SUMIFS(Customs!C:C,Customs!$A:$A,$A3)+SUMIFS(Signatory!C:C,Customs!$A:$A,$A3)+SUMIFS(Depart!C:C,Customs!$A:$A,$A3)

[#9]

@tonyan 

Dear Tonyan,

I just tried to understand your query. As far as I understand well, I made a dynamic table which you can find attached. The table is consisted of three sheets (same sheet names like yours). Also, I made one Summary Sheet. I found sales amounts based upon sales data for each sheet and made it shown on different columns. Later, I summed them up.

It is very possible that I don't answer to your question directly, However, I can tell you that in my opinion it is the best way to find the lookup data separately and sum them up.
As for your formula, I didn't understand why you are looking for close numbers like indicating "true". 

Good luck.

P.S.
=IFERROR(VLOOKUP(A2,DeparturBilled!$B$2:$E$51,4,FALSE),"")

=IFERROR(VLOOKUP(A2,'Non-SignatoryBilled'!$B$2:$E$51,4,FALSE),"")

=IFERROR(VLOOKUP(A2,CustomsBilled!$B$2:$E$51,4,FALSE),"")

[#10 · Accepted solution]

Check my sample file and comment. Thanks

[#11]

I use the Sumifs and that work great! Thanks