Forum Discussion

Catherine Petty's avatar
Catherine Petty
Copper Contributor
Nov 01, 2018

double hlookup in one formula

How can I combine two Hlookups in one formula

 

Worksheet One is my summary sheet

Worksheet Two contains UK Ltd data

Worksheet Three contains USA Inc data

 

Worksheet one the Summary sheet, currently has the following formula to return Ltd data 

=IFERROR(HLOOKUP(A4,'2018 Jan - June LTD'!$J$5:$BB$8,4,FALSE),"")

 

 

 

This works fine.

 

Rather than showing the US costs in a separate column of the summary sheet, I would like to add them to the above formula to return one value (UK LTD + USA INC) as a combined figure.

 

Is this possible or do I need to insert a new column for USA costs into my summary?

I was hoping to duplicate the above formula, amend the worksheet reference to the other worksheet and the result would add both values from Inc & Ltd together but it is returning a #VALUE ERROR

EXAMPLE:

=IFERROR(HLOOKUP(A4,'2018 Jan - June LTD'!$J$5:$BB$8,4,FALSE),"")+=IFERROR(HLOOKUP(A4,'2018 Jan - June INC'!$J$5:$BB$8,4,FALSE),"")

 

 

 

 

  • You don't need second "=", something like

    =IFERROR(HLOOKUP(A4,'2018 Jan - June LTD'!$J$5:$BB$8,4,FALSE)+HLOOKUP(A4,'2018 Jan - June INC'!$J$5:$BB$8,4,FALSE),"")
    • Catherine Petty's avatar
      Catherine Petty
      Copper Contributor

      thankyou for your reply.

       

      This seems to only work if there is relevant data in both worksheet, if not it returns a blank, when infact it should return the total of the one worksheet.

       

      Eg   Worksheet with relevant data £100.00 + Worksheet without relevant data £0.00 = £100

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        I see. When wrap by IFERROR each part

        =IFERROR(HLOOKUP(A4,'2018 Jan - June LTD'!$J$5:$BB$8,4,FALSE),0)+IFERROR(HLOOKUP(A4,'2018 Jan - June INC'!$J$5:$BB$8,4,FALSE),0)

         

Resources