# Lookup

#### Peter100

##### Well-known Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
{=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".

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

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

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

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.

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

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

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 :- 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.

Replies
1
Views
80
Replies
5
Views
152
Replies
3
Views
168
Replies
8
Views
242
Replies
3
Views
149

1,203,483
Messages
6,055,663
Members
444,806
Latest member
tofanexcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back