• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issue with Lookup

Gopinath Raghav

New Member
I am having trouble as I am trying to find the total sales for a region using VLOOKUP & INDEX+MATCH. But when using SUMIFS, I am getting the desired result. The sheet I am using is just a practice sheet as I am new to Excel. Kindly help me to resolve this issue.
 

Attachments

Peter Bartholomew

Well-Known Member
If you are trying to look up a sales figure
= VLOOKUP(A10,Table,4,0) or
= INDEX(Table[Sales], MATCH(A10, Table[Region Code], 0) )

If you want an entire record as an array
=VLOOKUP(A10,Table,{1,2,3,4},1) or
= INDEX(Table, MATCH(A10, Table[Region Code], 0), 0 )

If you want a sum of matching sales amounts
= SUMIFS(Table[Sales], Table[Region Code], A10 )
[if there is only one match the value is returned]
 

Gopinath Raghav

New Member
If you are trying to look up a sales figure
= VLOOKUP(A10,Table,4,0) or
= INDEX(Table[Sales], MATCH(A10, Table[Region Code], 0) )

If you want an entire record as an array
=VLOOKUP(A10,Table,{1,2,3,4},1) or
= INDEX(Table, MATCH(A10, Table[Region Code], 0), 0 )

If you want a sum of matching sales amounts
= SUMIFS(Table[Sales], Table[Region Code], A10 )
[if there is only one match the value is returned]
Thank you so much for taking your time to reply with formulas. I will try these and see if the desired result is achieved. Thanks again.
 
Top