Forum Discussion
Catherine Petty
Nov 01, 2018Copper Contributor
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 PettyCopper 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
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)