Index or Vlookup?

[TheDigital]

Hello there,

I'm trying to create a somewhat simple function where I grab active information from one sheet and populate it into another sheet.

• On the first sheet, I have a list for how much spending was done on an item and the reason the item was bought in a drop down menu.
• The second sheet, I'm trying to pull in information based off of how many times the item appeared and the total cost all together.

I've tried using vlookup but due to the sheet's layout, I cannot use this since it only looks at values to the right. When using Index, however, it works only for one list item and then I get a #ref for any other item.

Any assistance with this would be great! See below for example.

1st Sheet: Payments

Book1
	A	B
1	Date
2	Amount Paid	Reason
3	$50.00	Bills
4	$60.00	Events
5	$250.00	Bills
6	$783.00	Gift
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Payments

Cells with Data Validation
Cell	Allow	Criteria
B3:B32	List	=Reasons

2nd Sheet: Backend

Book1
	A	B
1	Date
2	Reasons	Total Amount Spent
3	Bills	$1,143.00
4	Business	#N/A
5	Clothing	#N/A
6	Eating Out	#N/A
7	Events	#REF!
8	Gas	#N/A
9	Gift	#REF!
10	Groceries	#N/A
11	Grooming	#N/A
12	Leisure	#N/A
13	Medical	#N/A
14	Other	#N/A
15	Pet	#N/A
16	Rent	#N/A
17	Subscription	#N/A
18	Trip	#N/A
19	Vacation	#N/A
Backend

Cell Formulas
Range	Formula
B3:B19	B3	=SUM(INDEX(Payments!$A$3:$A$32,,MATCH(Backend!A3,Payments!$B$3:B$32,0)))

Named Ranges
Name	Refers To	Cells
Reasons	=Backend!$A$3:$A$19	B3

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMIFS(Payments!$A$3:$A$32,Payments!$B$3:B$32,A3)

 

[TheDigital]

Hi & welcome to MrExcel.
How about

=SUMIFS(Payments!$A$3:$A$32,Payments!$B$3:B$32,A3)

Thanks for the quick reply. Worked. You're awesome! I had saw this something like this during my research but had no idea that this would work.

Very much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.