Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Better Alternative to VLookup and Hlookup

Best of Excel

Better Alternative to VLookup and Hlookup

by  Chance1234  Posted    (Edited  )

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

GROUP , SUBGROUP , VALUE
Click to expand...

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

Type, 1 to 2 Years Old, 3 To 5 Yers old, 7 To 8 Years Old
Click to expand...

And under Type (B3:B5)
Adoption
Care
Family
Click to expand...

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

Code: 
=SUMPRODUCT((Sheet1!B3:B11=B4)*(Sheet1!C3:C11 =D2)*(Sheet1!D3:D11))

And baddabing theres my answer, If i also use $ signs in my formula i can simply drag this across the grid to bring back all the values

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top