Vlookups with different criteria

[KuraiChikara]

Hello,
Say you have a bill called TRASH in Cell A2 and a total deduction of -$35 in F2
Then you have that bill again "TRASH" in Cell A115 with a different amount -$130 in F115.

The company is the same so that does not change.
However, when I conduct a VLOOKUP
=VLOOKUP(A2,'Excel Export'!$1:$1048576,6,FALSE)

It will find the first TRASH in cell A2 of -$35 but the second TRASH in cell A115 of -$130 doesnt even get reflected. The first amount of -$35 will be seen.

How can I show the different amounts with a Vlookup or am I out of luck and would have to change the search of the look up value?

 

[JonXL]

Hello,
Say you have a bill called TRASH in Cell A2 and a total deduction of -$35 in F2
Then you have that bill again "TRASH" in Cell A115 with a different amount -$130 in F115.

The company is the same so that does not change.
However, when I conduct a VLOOKUP
=VLOOKUP(A2,'Excel Export'!$1:$1048576,6,FALSE)

It will find the first TRASH in cell A2 of -$35 but the second TRASH in cell A115 of -$130 doesnt even get reflected. The first amount of -$35 will be seen.

How can I show the different amounts with a Vlookup or am I out of luck and would have to change the search of the look up value?

What do you mean by "show the different amounts with a Vlookup"?

You want to concatenate them? Add them? Show each one in a separate cell?

 

[AhoyNC]

VLOOKUP will only find the first record.
You might want to look at either Filter or Advance Filter on the ribbon.

If you want a formula you will need to use an array formula as in the example below.

If you have Excel 2010 or later you can use the formula in M2.
If not you will need to use a formula like cell K2. This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	E	F	G	H	I	J	K	L	M
1	Acct.			Amount			Find	Trash	Amounts
2	Trash			35			# of records	2	35		35
3	tyu			12					130		130
4	yui			1
5	tyu			25
6	Trash			130
7	rer			65
8
Sheet

 

[KuraiChikara]

Thank you that is what I was looking for. I was messing with an index match before you posted.

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[KuraiChikara]

I'm still a little stuck. Below, I have "Item" in "A," "# of records" (countif formula) in "B", "amount" in "C," and the formula in E. My info goes as far as 251 rows so I'm using the array formula after hitting CTRL+SHIFT+ENTER but instead of E2 coming back with -$5.27, it's just blank (because of the double quotes in the formula "").

I compared it to your example and all of my references seem similar but the amounts arent populating

{=IF(ROWS($E$2:E251)>$B$2,"",INDEX($C$2:$C$251,SMALL(IF($A$2:$A$251=$B$2,ROW($A$2:$A$251)-ROW($A$2)+1),ROWS($E$2:E251))))}



[TABLE="width: 391"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]# of records[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]#06481600[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($5.27)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#08836500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($3.85)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#15358800[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($42.37)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#16846300[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($6.66)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#19031200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($32.36)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#44582400[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($4.48)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#61119900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($4.27)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#64184300[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($17.68)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#66287600[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]($6.46)[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 391"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

I'm unclear on how you want to show your data.
In the example you posted there are no duplicate items in column A.

In the example below E2 is an array formula, but from your example you could just use an INDEX / MATCH formula like cell G2.

Excel Workbook
	A	B	C	D	E	F	G
1	Item	# of records	Amount		Formula		Formula
2	#06481600	1	($5.27)		-5.27		-5.27
3	#08836500	1	($3.85)		-3.85		-3.85
4	#15358800	1	($42.37)		-42.37		-42.37
5	#16846300	1	($6.66)		-6.66		-6.66
6	#19031200	1	($32.36)		-32.36		-32.36
7	#44582400	1	($4.48)		-4.48		-4.48
8	#61119900	1	($4.27)		-4.27		-4.27
9	#64184300	1	($17.68)		-17.68		-17.68
10	#66287600	1	($6.46)		-6.46		-6.46
Sheet

 

[KuraiChikara]

I do apologize for asking for help, I'm not that new to Excel but I'm not that good (better than a novice though). This whole thing is basically so I can setup a fancy way to do my billing.

Anyways, part of my issue is wording what I want. I don't know how to add visual examples like you so it's all text which can be harder to follow.

In your first example with the "Trash" that was excellent, while doing a bigger version of that with 250 columns of data I realized I was just re-referencing the amount of that item which I dont want.

What I'm trying to do is this:
You have 250 items in Column A and one item can be repeated X number of times, like TRASH (2). And all items have a financial amount. Overall, what I'm looking to do is find the total sum of each individual item.

Let's say we have ALL 250 items in Column A, Column B simply represents the financial amount of each item. Then say in column C you paste all items from column "A" but, use "remove duplicates" to get a single representation of all items.

Column D might have a COUNTIF formula to actually find the amount of items in Column A by Referencing it to Column C to find the total (IE: Out of 250 items, 50 of them are called TRASH).

Finally, Column E has the final total of each item, in some way it will formulate that "Oh, there are 50 items called Trash, the total of all items added up under Trash come to a total of $X."

That's what I don't know how to do.

 

[yellow_orange]

please share the excel file. it can be a dummy file just for others to have a look at the problem and share solutions.

the formula solution will be very long.
step 1: write a fancy formula to show unique values only (can be done manually by copying and pasting then remove duplicates)
step 2: use countifs and sumifs formula

or you can just do a pivot table.

 

[yellow_orange]

or make a drop down list (e.g., product items) then apply countifs and sumifs