VLOOKUP: How to return unique values from one column if constant value is in another column on separate sheet based on constant value cell

[kznmrexcel]

I have a list of scanned inventory where we are receiving replacements of broken Chromebooks on a specific trouble ticket. The worksheet, AssetRecd, with the replacements being scanned into our inventory as they arrive, looks like this:

Asset received and scanned	Scan date	Ticket
T10024778	2/4/25	76725
T10027724	2/4/25	76725
T10023540	2/4/25	76725
T20033540​	2/21/25​	77173

​

A separate sheet, BrokenCBsenttoIT, which shows the broken devices being sent out for repair, looks like this:

Asset bc	Ticket	Date of ticket	Date of sent device recd *by* IT at district office	Sent to or collected by	Ticket # - replacements sent to GV	Replacement bc recd
T10048434	76725	1/31/2025	2/4/25	IT staff	76725
T10020505	76725	1/31/2025	2/4/25	IT staff	76725
76393378	76725	1/31/2025	2/4/25	IT staff	76725
T10014789	77173	2/12/2025	2/15/25	Pony	77173
T10011287	77173	2/12/2025	2/15/25	Pony	77173

We would like to return a specific asset barcode in each row from the AssetRecd worksheet next to each line item shown on the BrokenCBsenttoIT worksheet if the ticket # matches. The twist is that, for instance, asset barcode #T10048434 listed in the first column of BrokenCBsenttoIT does not match a specific barcode on AssetRecd. We only have the ticket number, 76725, on each sheet.

We want to return or "use" each replacement asset barcode only one time on one line from AssetRecd to the BrokenCBsenttoIT, even though lining up the asset barcodes will be random as long as the ticket number matches. If possible, our results in the Replacement bc recd column would look like what's shown below, and should we not have enough devices scanned into the AssetRecd worksheet, we "run out" and do not show a result for every line item with the same ticket number on the BrokenCBsenttoIT sheet.

Is this possible?

Asset bc	Ticket	Date of ticket	Date of sent device recd *by* IT at district office	Sent to or collected by	Ticket # - replacements sent to GV	Replacement bc recd
T10048434	76725	1/31/2025	2/4/25	IT staff	76725	T10024778
T10020505	76725	1/31/2025	2/4/25	IT staff	76725	T10027724
76393378	76725	1/31/2025	2/4/25	IT staff	76725	T10023540
T10014789	77173	2/12/2025	2/15/25	Pony	77173	T20033540​
T10011287	77173	2/12/2025	2/15/25	Pony	77173

Note that the last cell is blank, indicating that we did not yet receive a replacement device for T10011287.

Is this possible?

 

[hagia_sofia]

Hello, could you please clarify whether either there are some matches of tickets AND barcodes and you would like to "randomly" assign only those cases where this is not true or all barcodes are to be assigned "randomly" (provided that there is a match between tickets)? Also, is it supposed to be for Excel 2016 as stated in your profile or a newer version?

 

[kznmrexcel]

Excel 2016 (also used on Google Sheets, which seems to use the same functions well)
There is no correlation between a barcode of a device being sent for repair and another one sent back as replacement, other than they are both listed for the same repair ticket number. We're interested in a way to see if, for instance, we sent in 3 devices but only got back 2 on the same ticket. If we can randomly assign individual barcodes from the list of replacements going from the top to the bottom to individual barcodes sent out for repair, the thinking is that, if we didn't receive the correct number, some of the items sent out for repair would show that they still need to be replaced.

 

[Sam_D_Ben]

@hagia_sofia , Apologies for the interruption.
I was wondering if this works-

VLOOKUP_How to return unique values from one column if constant value is in another column_kznmrexcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	AssetRecd					BrokenCBsenttoIT
2	Asset received and scanned	Scan date	Ticket			sset bc	Ticket	Date of ticket	Date of sent device recd *by* IT at district office	Sent to or collected by	Ticket # - replacements sent to GV	Replacement bc recd
3	T10024778	02/04/25	76725			T10048434	76725	01/31/25	02/04/25	IT staff	76725	T10024778
4	T10027724	02/04/25	76725			T10020505	76725	01/31/25	02/04/25	IT staff	76725	T10027724
5	T10023540	02/04/25	76725			76393378	76725	01/31/25	02/04/25	IT staff	76725	T10023540
6	T20033540	02/12/25	77173			T10014789	77173	02/12/25	02/15/25	Pony	77173	T20033540
7						T10011287	77173	02/12/25	02/15/25	Pony	77173	0
Sheet1

Cell Formulas
Range	Formula
L3:L7	L3	=INDEX($A$3:$A$7, AGGREGATE(15, 6, ROW($A$3:$A$7)-ROW($A$3)+1 / ($K$3:$K$7=C3) * (COUNTIF($C$3:C3, C3)=COUNTIF($K$3:$K$7, C3)), 1))
Dynamic array formulas.