# 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

• 18 KB Views: 6

#### GraH - Guido

##### Well-Known Member
Why is it an issue, when you state "when using SUMIFS, I am getting the desired result"?

#### 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
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

##### 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.