Lookup

[Peter100]

I am wanting to perform a lookup but want to add three column indexes together

ie. VLOOKUP($A7,$C$15:$CD$74,15) would give me the result in column 15 of the array but I want the sum of columns 15,16 & 17 as the result.

Anyone know a crafty way around it without creating a separate column for the sum of 15,16 & 17

 

[Mark W.]

{=SUM(VLOOKUP($A7,$C$15:$CD$74,{15,16,17}))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[Peter100]

Hi Mark

I've tried that but it still nly returns the value of the first referenced column
(I'm fairly used to entering arrays so the {} aren't the problem

 

[Aladin Akyurek]

On 2002-10-15 17:11, Peter100 wrote:
I am wanting to perform a lookup but want to add three column indexes together

ie. VLOOKUP($A7,$C$15:$CD$74,15) would give me the result in column 15 of the array but I want the sum of columns 15,16 & 17 as the result.

Anyone know a crafty way around it without creating a separate column for the sum of 15,16 & 17

Name (via the Name Box) the range $C$15:$CD$74 Table and use:

=SUM(INDEX(Table,MATCH($A7,INDEX(Table,0,1)),15):INDEX(Table,MATCH($A7,INDEX(Table,0,1)),17))

It would be much nicer to have the MATCH($A7,INDEX(Table,0,1)) bit in a cell of its own and use that cell in the formula above which is swift and efficient.

You could of course use the SETV/GETV pair if you have installed the morefunc.xll...

=SUM(INDEX(Table,SETV(MATCH($A7,INDEX(Table,0,1))),15):INDEX(Table,GETV(),17))

 

[Mark W.]

On 2002-10-15 17:33, Peter100 wrote:
Hi Mark

I've tried that but it still nly returns the value of the first referenced column
(I'm fairly used to entering arrays so the {} aren't the problem

Sorry, Peter... I was too quick "on the draw". Perhaps you meant the 1st, 2nd and 3rd columns of a lookup table that begins in column 15. Use...

{=SUM(VLOOKUP($A7,$C$15:$CD$74,{1,2,3}))}

...and, make sure you observe this note...

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

Isn't this solution sweet! Array formulas are soooo much fun!
This message was edited by Mark W. on 2002-10-15 17:46

 

[Aladin Akyurek]

Isn't this solution sweet! Array formulas are soooo much fun!

Sure! But, I wouldn't advise Peter to copy it down for many lookup values he seems to have.

An INDEX formulation is not only sweet, it's also darn fast.

 

[Mark W.]

MATCH isn't any faster than VLOOKUP... And which has fewer function calls?

Who said anything about copying down?
This message was edited by Mark W. on 2002-10-15 17:59

 

[Dave Patton]

If the data is in the 15-17th columns of the LookupTable, Aladin's and Mark's initial suggestion should give the same result.
This message was edited by Dave patton on 2002-10-15 18:06

 

[Peter100]

Hi Mark & Aladin

Mark :- You were right in your first thought but I still can't make it happen
I've ended up using
VLOOKUP($A7,$C$15:$CD$74,15)+VLOOKUP($A7,$C$15:$CD$74,16)+VLOOKUP($A7,$C$15:$CD$74,17)
But I'll keep at it

Aladin :- Have never used index but am intrigued at your comment that it is "Dam Fast" as I indeed have many lookups in the workbook and it is a heavy overhead when recalculating after making an entry.

I'll atempt to understand the logic of your formula as if as you say Index is that much faster I could be converted.

 

[Aladin Akyurek]

Aladin :- Have never used index but am intrigued at your comment that it is "Dam Fast" as I indeed have many lookups in the workbook and it is a heavy overhead when recalculating after making an entry.

I'll atempt to understand the logic of your formula as if as you say Index is that much faster I could be converted.

Try & you'll see.