Issue with Lookup

[Gopinath Raghav]

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.

 

[GraH - Guido]

Why is it an issue, when you state "when using SUMIFS, I am getting the desired result"?

 

[Peter Bartholomew]

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]

Why is it an issue, when you state "when using SUMIFS, I am getting the desired result"?

Well, even though I am getting the desired result in SUMIFS, I don't want it to be limited to one particular function. Thank you for your response.

 

[Gopinath Raghav]

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.