HLOOKUP to return entire sum of column

[Julesdude]

Hi there. I need help trying to create a simple(ish) forumula. One cell needs to have a formula that uses the HLOOKUP function for range V3:AK3. But what I want it to return is not the value of a particular cell but the sum of the entire column that is identified in this HLOOKUP range.

I also need to trap any errors so if a value is 0 or is an error, to just display a blank.

Can anyone please help?

 

[Gerald Higgins]

Do you need to identify the relevant column the same way that HLOOKUP does, by reference to the values in the first row ?

 

[Jon von der Heyden]

Try:

=SUM(INDEX($V:$AK,0,MATCH(lookup_val,$V$3:$AK$3,0)))

 

[baitmaster]

INDIRECT should solve this, either by itself (e.g. with a named range), or in combination with a lookup table

 

[Jon von der Heyden]

And in case the lookup value isn't found:

=IF(COUNTIF(lookup_val,$V$3:$AK$3),SUM(INDEX($V:$AK,0,MATCH(lookup_val,$V$3:$AK$3,0))),"")

 

[Julesdude]

Thanks everyone for the input. I've tried using the examples but it's not returning the correct values and I think there may be a problem using the COUNTIF to return a blank if false -it will always return true because it is guaranteed that the number of occurances will be greater than 0.

I'll try and make what I need clearer. I have cell D44. I need to use the value in cell B44 to search for the same value in a range. That range is J3:EQ3 (these are column references for a table). When it finds this match, I need that entire column totaled and that total returned back to original cell D44, that I am writing the formula in.

If the value returned is 0 or an error, then D44 must be blank.

 

[Gerald Higgins]

The value that's in B44 - can it appear ANYWHERE in the range J3:EQ3, or will it appear only on row 3 ?

 

[Julesdude]

Hi Gerald. It will appear anywhere in the range J3:EQ3. So if cell B44 has "Hello" written in it then it needs to find "hello" in that range and use the column it's in as an index to total every value in that column.

So if it finds "hello" in L3, it then needs to SUM L4:L65536, returning the value back to D44.

 

[Jon von der Heyden]

I still think my suggestion works. Here's an example.

Excel Workbook
	A	B	C	D	E
1	Lookup Val	abc
2	Result	5674
3
4	a	ab	abc	abcd	abcde
5	499	360	240	407	843
6	423	955	435	956	312
7	257	254	364	456	932
8	63	564	102	372	747
9	630	592	801	484	127
10	20	743	43	596	972
11	649	333	989	536	700
12	780	393	542	138	617
13	447	610	275	142	248
14	155	96	451	339	734
15	626	938	151	602	360
16	588	581	787	646	501
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=IF(COUNTIF(A4:E4,B1),SUM(INDEX($B:$F,0,MATCH(B1,$A$4:$E$4,0))))

 

[Jon von der Heyden]

Apologies, error in my previous sample. Please refer to this one:

Excel Workbook
	A	B	C	D	E
1	Lookup Val	abc
2	Result	5180
3
4	a	ab	abc	abcd	abcde
5	499	360	240	407	843
6	423	955	435	956	312
7	257	254	364	456	932
8	63	564	102	372	747
9	630	592	801	484	127
10	20	743	43	596	972
11	649	333	989	536	700
12	780	393	542	138	617
13	447	610	275	142	248
14	155	96	451	339	734
15	626	938	151	602	360
16	588	581	787	646	501
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A$4:$E$4,$B$1),SUM(INDEX($A:$E,0,MATCH($B$1,$A$4:$E$4,0))),"")