Forum Discussion

tonyan's avatar
tonyan
Copper Contributor
Nov 02, 2020
Solved

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

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?

excel

10 Replies

  • Ilgar_Zarbaliyev's avatar
    Ilgar_Zarbaliyev
    MCT
    Nov 02, 2020

    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),"")

     

     

  • tonyan's avatar
    tonyan
    Copper Contributor
    Nov 02, 2020
    I am not able to share sheet due to company policy. Are you still able to help me?
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 02, 2020

    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?

    • tonyan's avatar
      tonyan
      Copper Contributor
      Nov 02, 2020

      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?

      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 02, 2020

        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.

         

Resources