Personally i cant stand Vlookup & Hlookup formulas in excel, as far as im aware its just fodder left over from the days of Lotus123
A lot better way is to use sumproduct as it avoids a lot of the limitations
To give an example of this
On Sheet1 Starting at cell B2
Enter in the following column headings
Quote:
GROUP , SUBGROUP , VALUE
Then under The headings enter in the following data
CODE
Adoption, 1 to 2 Years Old , 4 Adoption, 3 to 5 Years old, 5 Adoption, 7 to 8 Years old , 2 Care, 1 to 2 Years Old , 4 Care, 3 To 5 years Old , 2 Care 7 To 8 Years Old, 3 Family 1 to 2 Years Old, 6 Family 3 To 5 Years Old, 4 Family 7 To 8 Years Old , 3
Etc etc etc etc
Now On Sheet2 starting at Cell B2
Enter in the following column headings
Quote:
Type, 1 to 2 Years Old, 3 To 5 Yers old, 7 To 8 Years Old
And under Type (B3:B5)
Quote:
Adoption Care Family
NOw using the sumproduct formula, if i want to find out how many 3 to 5 Years Olds are in Care, i enter in the formula in D4 on sheet2 as follows
Talk To Other Members