Lookup Next Value in Column Based on Criteria

B

brett781

New Member
Joined
Dec 19, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
I believe that this is easier than I'm trying to make it, but I'm trying to see the next item in a dataset based on criteria. What I would like to see is the ability to look up Model A and then show the next Serial Number available. Some insight into the need, I have a dataset of Models and Serial numbers that are in our inventory and a list of Models that we need to fulfill. I need to be able to show this is the model we need to fulfill and here is the serial number that we have to fulfill the order.

Dataset:
ModelSerial
A1234
B1235
B1237
A1236
C1238

Return:
ModelSerial
A1234
A1236
B1235
B1237
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
not 100% sure thius is what you need
But you say with criteria, and next value
But not what you show

anyway

this will filter the dataset based on criteria

Book1
ABCDEF
1ModelSerialcriteriaRESULT
2A1234aA1234
3B1235A1236
4B1237
5A1236
6C1238
Sheet1
Cell Formulas
RangeFormula
E2:F3E2=FILTER(A2:B6,A2:A6=D2)
Dynamic array formulas.
 
Upvote 0
etaf said:
not 100% sure thius is what you need
But you say with criteria, and next value
But not what you show

anyway

this will filter the dataset based on criteria

Book1
ABCDEF
1ModelSerialcriteriaRESULT
2A1234aA1234
3B1235A1236
4B1237
5A1236
6C1238
Sheet1
Cell Formulas
RangeFormula
E2:F3E2=FILTER(A2:B6,A2:A6=D2)
Dynamic array formulas.
Click to expand...

I may not have shown that correctly. I have a list of about 1500 models with serial numbers in inventory and a list of about 300 models that we need a serial number assigned to. Trying to match the model number list that I have (which have specific purchase orders tied to them) with the list of our models in inventory and fill in the serial number we need attached to that model. See if below makes more sense.

Models which Serial numbers are needed:
Trailer Item Number
14OA-20BK-8SIR
14OA-20BK-8SIR
14OA-20BK-8SIR
14OA-24BK-8SIR
14OA-24BK-8SIR
14OA-24BK-8SIR
10PI-20BK
70PI-14XBK4RG
10PI-20BK
60PI-14BK4RG
35SA-12BK4RG
10PI-16BK
60PI-14BK4RG
35SA-12BK4RG
10PI-16BK
60PI-14BK4RG
35SA-12BK4RG
35SA-12BK4RG
70PI-16XBK4RG
60PI-14BK4RG
70PI-16XBK4RG
60PI-14BK4RG
35SA-14BK4RG
70PI-16XBK4RG
60PI-14BK4RG

Model and Serial Numbers in inventory:
Item NumberSerial Number
10CH-1601047954
10CH-1601047957
10CH-1600990989
10CH-1601099912
10CH-1601047955
10CH-1600983306
10CH-1601047956
10CH-16BKDT01135936
10CH-16BKDT01095005
10CH-16BKDT01141841
10CH-1801059402
10CH-1801047961
10CH-1801047962
10CH-1801047963
10CH-1801047958
10CH-1801015837
10CH-1801141188
10CH-1800983309
10CH-1801047959
10CH-1801062822
10CH-1801157178
10CH-1801047960
10CH-18BKDT01122001
10CH-18BKDT01163766
10CH-18BKDT01143648
10CH-18BKDT01143649
10CH-18BKDT01143785
10CH-18BKDT01143650
10CH-18BKDT01122000
10CH-18BKDT01145728
10CH-18BKDT01141913
10CH-18BKDT01152212
10CH-18BKDT01151579
 
Upvote 0
brett781 said:
I may not have shown that correctly. I have a list of about 1500 models with serial numbers in inventory and a list of about 300 models that we need a serial number assigned to. Trying to match the model number list that I have (which have specific purchase orders tied to them) with the list of our models in inventory and fill in the serial number we need attached to that model. See if below makes more sense.

Models which Serial numbers are needed:
Trailer Item Number
14OA-20BK-8SIR
14OA-20BK-8SIR
14OA-20BK-8SIR
14OA-24BK-8SIR
14OA-24BK-8SIR
14OA-24BK-8SIR
10PI-20BK
70PI-14XBK4RG
10PI-20BK
60PI-14BK4RG
35SA-12BK4RG
10PI-16BK
60PI-14BK4RG
35SA-12BK4RG
10PI-16BK
60PI-14BK4RG
35SA-12BK4RG
35SA-12BK4RG
70PI-16XBK4RG
60PI-14BK4RG
70PI-16XBK4RG
60PI-14BK4RG
35SA-14BK4RG
70PI-16XBK4RG
60PI-14BK4RG

Model and Serial Numbers in inventory:
Item NumberSerial Number
10CH-1601047954
10CH-1601047957
10CH-1600990989
10CH-1601099912
10CH-1601047955
10CH-1600983306
10CH-1601047956
10CH-16BKDT01135936
10CH-16BKDT01095005
10CH-16BKDT01141841
10CH-1801059402
10CH-1801047961
10CH-1801047962
10CH-1801047963
10CH-1801047958
10CH-1801015837
10CH-1801141188
10CH-1800983309
10CH-1801047959
10CH-1801062822
10CH-1801157178
10CH-1801047960
10CH-18BKDT01122001
10CH-18BKDT01163766
10CH-18BKDT01143648
10CH-18BKDT01143649
10CH-18BKDT01143785
10CH-18BKDT01143650
10CH-18BKDT01122000
10CH-18BKDT01145728
10CH-18BKDT01141913
10CH-18BKDT01152212
10CH-18BKDT01151579
Click to expand...

Please post some expected results from the above data set
 
Upvote 0
Sufiyan97 said:
Please post some expected results from the above data set
Click to expand...
See below.

Serial Numbers Needed:
Purchase OrderModel
12310CH-20BKDT
12410CH-20BKDT
12510ET-16BK-MR
12610ET-18BK-MR
12710LX-12BK6SIR
12810LX-12BK6SIR
12910OA-18BK-8SIR
13010OA-18BK-8SIR
13110PI-16BK
13210PI-16BK
13310PI-16BK
13410PI-16BK
13510PI-20BK
13610PI-20BK
13710PI-20BK
13810SR-12XLBK6SIR
13910SR-12XLBK6SIR
14010SR-12XLBK6SIR
14110SR-12XLBK6SIR

Serial Numbers Available
ModelSerial
10CH-20BKDT01172026
10CH-20BKDT01172027
10CH-20BKDT01172025
10ET-16BK-MR01072782
10ET-16BK-MR01022394
10ET-16BK-MR01035525
10ET-18BK-MR01039948
10ET-18BK-MR01018909
10ET-18BK-MR01072790
10LX-12BK6SIR01120060
10LX-12BK6SIR01033766
10LX-12BK6SIR01033767
10LX-12BK6SIR01033780
10LX-12BK6SIR01033781

Expected Output:
Purchase OrderTrailer Item NumberSerial Number
12310CH-20BKDT01172026
12410CH-20BKDT01172027
12510ET-16BK-MR01072782
12610ET-18BK-MR01039948
12710LX-12BK6SIR01120060
12810LX-12BK6SIR01033766
 
Upvote 0
Try

Book7
ABCDEFG
2Purchase OrderModelSerial NumberModelSerial
312310CH-20BKDT117202610CH-20BKDT1172026
412410CH-20BKDT117202710CH-20BKDT1172027
512510ET-16BK-MR107278210CH-20BKDT1172025
612610ET-18BK-MR103994810ET-16BK-MR1072782
712710LX-12BK6SIR112006010ET-16BK-MR1022394
812810LX-12BK6SIR103376610ET-16BK-MR1035525
912910OA-18BK-8SIR 10ET-18BK-MR1039948
1013010OA-18BK-8SIR 10ET-18BK-MR1018909
1113110PI-16BK 10ET-18BK-MR1072790
1213210PI-16BK 10LX-12BK6SIR1120060
1313310PI-16BK 10LX-12BK6SIR1033766
1413410PI-16BK 10LX-12BK6SIR1033767
1513510PI-20BK 10LX-12BK6SIR1033780
1613610PI-20BK 10LX-12BK6SIR1033781
1713710PI-20BK 
1813810SR-12XLBK6SIR 
1913910SR-12XLBK6SIR 
2014010SR-12XLBK6SIR 
2114110SR-12XLBK6SIR 
22
Sheet1
Cell Formulas
RangeFormula
C3:C21C3=IFERROR(INDEX($F$3:$F$16,AGGREGATE(15,6,(ROW($F$3:$F$16)-ROW($F$3)+1)/($E$3:$E$16=B3),COUNTIF($B$3:B3,B3))),"")
 
Upvote 0
Sufiyan97 said:
Try

Book7
ABCDEFG
2Purchase OrderModelSerial NumberModelSerial
312310CH-20BKDT117202610CH-20BKDT1172026
412410CH-20BKDT117202710CH-20BKDT1172027
512510ET-16BK-MR107278210CH-20BKDT1172025
612610ET-18BK-MR103994810ET-16BK-MR1072782
712710LX-12BK6SIR112006010ET-16BK-MR1022394
812810LX-12BK6SIR103376610ET-16BK-MR1035525
912910OA-18BK-8SIR 10ET-18BK-MR1039948
1013010OA-18BK-8SIR 10ET-18BK-MR1018909
1113110PI-16BK 10ET-18BK-MR1072790
1213210PI-16BK 10LX-12BK6SIR1120060
1313310PI-16BK 10LX-12BK6SIR1033766
1413410PI-16BK 10LX-12BK6SIR1033767
1513510PI-20BK 10LX-12BK6SIR1033780
1613610PI-20BK 10LX-12BK6SIR1033781
1713710PI-20BK 
1813810SR-12XLBK6SIR 
1913910SR-12XLBK6SIR 
2014010SR-12XLBK6SIR 
2114110SR-12XLBK6SIR 
22
Sheet1
Cell Formulas
RangeFormula
C3:C21C3=IFERROR(INDEX($F$3:$F$16,AGGREGATE(15,6,(ROW($F$3:$F$16)-ROW($F$3)+1)/($E$3:$E$16=B3),COUNTIF($B$3:B3,B3))),"")
Click to expand...

Perfect. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
MinIfs Formula (or alternative approach) to find the lowest value per row based on 2 criteria
Replies
3
Views
224
Peter_SSs
Peter_SSs
R
  • Solved
Need help with a formula
Replies
4
Views
148
Fluff
Fluff
R
  • Question
2 column lookup formula to reference new data
Replies
1
Views
221
awoohaw
awoohaw
L
  • Question
Can scripting dictionary alert if certain criteria are met?
Replies
1
Views
165
Lil Stinker
L
S
  • Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
705
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,216,113
Messages
6,128,905
Members
449,478
Latest member
Davenil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top