Nov 02 2020 09:40 AM
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?
Nov 02 2020 10:03 AM
@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?
Nov 02 2020 10:04 AM
Nov 02 2020 10:10 AM
Nov 02 2020 10:22 AM
@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?
Nov 02 2020 10:46 AM - edited Nov 02 2020 10:47 AM
@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.
Nov 02 2020 11:03 AM
@Ilgar_Zarbaliyev see sheet
Nov 02 2020 11:15 AM
@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)
Nov 02 2020 11:15 AM
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),"")
Nov 02 2020 11:16 AM
SolutionNov 03 2020 09:45 AM
Nov 02 2020 11:16 AM
Solution