return data from range in lookup formula

[ajdesigns]

Hi
I have a vlookup:
=IF(ISERROR(VLOOKUP(H$6&$H$4&$A7&$A$20&"0",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$6&$H$4&$A7&$A$20&"0",TERRITORY,2,FALSE))
where H6 =month, H4=Year, A7 = prod group A20 = Territory.
This returns the sales per month for each territory by prod code.
But now I need rest of the world and need to add the total for all remaining territories, I have 12 territories to consider I tried to put a range in this statement rather than adding on another lookup for each territory, but it does not add up all territories in the range, instead it gives me a zero qty when I know there is data there.
Is there any way to do this?

Territories are in A20:A32

=IF(ISERROR(VLOOKUP(H$6&$H$4&$A7&$A$20:$A$32&"0",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$6&$H$4&$A7&$A$20:$A$32&"0",TERRITORY,2,FALSE))
AJD

 

[SkipVought]

AJD,

Consider using the OFFSET function. The OFFSET function has 5 arguments...
[tt]
1: Anchor reference --your starting pointer
2: Rows offset from anchor
3: Cols offset from anchor
4: Number of rows in the offset range
5: Number of cols in the offset range
[/tt]
Ususlly 1) is the upper-left cell in the table.
2/3) can be obtained using a MATCH function to determine an offset starting point for the offset range
4/5) can be counts of how deep the range is using COUNTA or SUMPRODUCT or the like.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[ETID]

Try incorperating the match() function to return an integer equal to the offset value for the vlookup.

example:

=vlookup(a1,$r$1:$z$100,match(b1,$r$1:$z$1,0)-17,false)

...Where b1 is a variable that can be found in the range R1:Z1 and the -17 adjusts the column number of the lookup range to account for R being the 18th letter in the alphabet.

 

[ajdesigns]

CHANGED MY LOOKUP TO
=IF(ISERROR(VLOOKUP(H$8&$H$6&$A9&OFFSET(A1,31,0,12,0)&"0",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$8&$H$6&$A9&OFFSET(A1,31,0,12,0)&"0",TERRITORY,2,FALSE))+IF(ISERROR(VLOOKUP(H$8&$H$6&$A9&OFFSET(A1,31,0,12,0)&"C",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$8&$H$6&$A9&OFFSET(A1,31,0,12,0)&"C",TERRITORY,2,FALSE))

GIVING THAT THE RANGE i WANT TO COMPARE IS IN THE CELLS A32:A43.
i AM STILL GETTING zERO'S

 

[GlennUK]

If you need the sum of 12 lookups, you are going to have to use 12 look-ups. You can't input a range for VLOOKUP to fetch stuff on.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Zathras]

This sounds like the use of database techniques would be a better fit. It would help if you could post a little bit of test data along with what you want the results to look like.

Another possible solution may be found with the use of a Pivot Table. It's difficult to say what is best because you haven't painted a clear picture of what exactly it is you are doing.

 

[GlennUK]

Instead of using VLOOKUPs, you could attach this problem from a different angle ... something similar to this could work :

SUMPRODUCT((NOT(ISNA(MATCH(TERRITORY_FIRST_COLUMN,TWELVE_TO_MATCH,0))))*(TERRITORY_COLUMN_TO_FETCH))

Where TWELVE_TO_MATCH refers to 12 cells contain the 12 things you want to find in the TERRITORY_FIRST_COLUMN range, and then the formula sums the corresponding figures from the TERRITORY_COLUMN_TO_FETCH column.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.