Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Office FAQ

Best of Excel

Better Alternative to VLookup and Hlookup by Chance1234
Posted: 23 Mar 06

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



Then under The headings enter in the following data


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

And under Type (B3:B5)



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


=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

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close