sum several vlookups

SamS · May 13th, 2002, 10:44 PM

Is there a way to sum in one formula several lookups? The formula needs to look up the same Lookup_Value and Column_Index but a different Table_Array eg

=vlookup($A$9,TableA,2,0)+vlookup($A$9,TableB,2,0)+vlookup($A$9,TableC,2,0)

If I get this one it will be 2 new ways of using vlookup I've learn't this week (last one posted by Chris).

Many thanks in advance.

smozgur · May 14th, 2002, 01:31 AM

You are on the right way, you just need to check if corresponding value exists and also VLOOKUP colum value starts from 1, not 0. If your return value in column 2 in lookup range then you can use a formula like below.

=if(isna(vlookup($A$9,TableA,2,FALSE)),0,vlookup($A$9,TableA,2,FALSE))+if(isna(vlookup($A$9,TableB,2,FALSE)),0,vlookup($ A$9,TableB,2,FALSE))+if(isna(vlookup($A$9,TableC,2,FALSE)),0,vlookup($A$9,TableC,2,FALSE))

Regards

smozgur · May 14th, 2002, 01:35 AM

Hmm, some explanation:

FALSE in VLOOKUP function will help it return nothing (#NA) if lookup value doesnot exist in lookup range, otherwise it will return a value of closer criteria to lookup value.

ISNA(..) will check if a real value is returned or #NA returned. And if it is #NA then setting it 0 ise good way in an addition formula i think.

SamS · May 14th, 2002, 03:42 AM

Thanks smozgur, what I really wanted was to somehow abbreviate the entire formula as I need to add up to 12 lookups at times and seeing that 2 of the 3 criteria were constant and only the table array changed I thought there might have been some way of doing this. Chris Davison posted an unusual vlookup formula that I hadn't seen before the other day and thought that this might also have tackled likewise.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 10:44 PM	#1
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Is there a way to sum in one formula several lookups? The formula needs to look up the same Lookup_Value and Column_Index but a different Table_Array eg =vlookup($A$9,TableA,2,0)+vlookup($A$9,TableB,2,0)+vlookup($A$9,TableC,2,0) If I get this one it will be 2 new ways of using vlookup I've learn't this week (last one posted by Chris). Many thanks in advance.

May 14th, 2002, 01:31 AM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	You are on the right way, you just need to check if corresponding value exists and also VLOOKUP colum value starts from 1, not 0. If your return value in column 2 in lookup range then you can use a formula like below. =if(isna(vlookup($A$9,TableA,2,FALSE)),0,vlookup($A$9,TableA,2,FALSE))+if(isna(vlookup($A$9,TableB,2,FALSE)),0,vlookup($ A$9,TableB,2,FALSE))+if(isna(vlookup($A$9,TableC,2,FALSE)),0,vlookup($A$9,TableC,2,FALSE)) Regards

May 14th, 2002, 01:35 AM	#3
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Hmm, some explanation: FALSE in VLOOKUP function will help it return nothing (#NA) if lookup value doesnot exist in lookup range, otherwise it will return a value of closer criteria to lookup value. ISNA(..) will check if a real value is returned or #NA returned. And if it is #NA then setting it 0 ise good way in an addition formula i think.

May 14th, 2002, 03:42 AM	#4
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Thanks smozgur, what I really wanted was to somehow abbreviate the entire formula as I need to add up to 12 lookups at times and seeing that 2 of the 3 criteria were constant and only the table array changed I thought there might have been some way of doing this. Chris Davison posted an unusual vlookup formula that I hadn't seen before the other day and thought that this might also have tackled likewise.