Which Formula?

[Dave]

What I want to do is>
Search through product codes for a certain code… Then I have it return the
transaction number…
So, I created a Vlookup to do so...
(EXAMPLE BELOW)

My problem is…
When I use my Vlookup formula and drag it down, it will repeat the same
transaction for several rows (probably based of # of rows until the next
transaction with that product code) before listing the next transaction.
I would like for each transaction to only be listed once and in subsequent
order.
How can I eliminate this repetition…

Example- Searching for product code 21…
DATA SHEET
A-Product Code B-Transaction #
18 AX144
21 AT888
55 BT444
89 EE789
21 BW123
10 CR559

VLOOKUP FORMULA SHEET
A- (=Vlookup(21, A1:B1000, 2, false)
AT888
AT888
BW123
BW123
BW123

WHAT I WOULD LIKE IS JUST:
AT888
BW123

 

[TomPl]

VLOOKUP will not do what you want. Have you considered using
<Data><Filter><Autofilter> on your data to filter for the product code you
want?

Tom

 

[T. Valko]

I recommend using AutoFilter but if you want a dynamic formula solution....

A2:A100 = Product Code
B2:B100 = Transaction #
D2 = lookup Product Code = 21

Enter this array formula** in E2 and copy down*** until you get blanks:

=IF(ROWS(E$2:E2)<=COUNTIF(A:A,D$2),INDEX(B$2:B$100,SMALL(IF(A$2:A$100=D$2,ROW(B$2:B$100)),ROWS(E$2:E2))-MIN(ROW(B$2:B$100))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

*** you have to copy the formula to a number of cells that is at least equal
to the max count of any product code. For example, if product code 21
appears 10 times in col A and that is the most times out of all the product
codes then you have to copy the formula to at least 10 cells.