Vlookup, Bring up certain Info. only???

[Dave]

Please help, I have work hard to make this easy to understand. (example below)

Currently, I am using a Vlookup formula to… Search through product codes for
a certain code… Then I have it return the transaction number…
So, the product codes repeats itself whenever is involved in a transaction.
However, transactions only occur once. (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.

How can I eliminate this repetition process…SO, How can I create a Vlookup
that will…Search through product codes for a certain code, Return the
transaction number…THEN go on and only list the next transaction with that
product code.

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:
AT888
BW123

 

[Pete_UK]

You could add a column C to what you already have and use this to give
you a unique reference for each product code entry, i.e. put this in
C2 of your data sheet:

=A2&"_"&countif(A$2:A2,A2)

and then copy down as far as required. Then in your other sheet you
could enter 21 in cell A1, for example, and this formula in A2:

=IF(A$1="","",IF(ISNA(MATCH(A$1&"_"&ROW(A1),DATA!C:C,0)),"",INDEX(DATA!
B:B,MATCH(A$1&"_"&ROW(A1),DATA!C:C,0)))

Copy this down as far as you think you might need, then you can just
enter another product code in A1.

Hope this helps.

Pete