# Lookup Next Value in Column Based on Criteria



## brett781 (Dec 19, 2022)

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:

ModelSerialA1234B1235B1237A1236C1238

Return:

ModelSerialA1234A1236B1235B1237


----------



## etaf (Dec 19, 2022)

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 

Book1ABCDEF1ModelSerialcriteriaRESULT2A1234aA12343B1235A12364B12375A12366C1238Sheet1Cell FormulasRangeFormulaE2:F3E2=FILTER(A2:B6,A2:A6=D2)Dynamic array formulas.


----------



## brett781 (Dec 19, 2022)

etaf said:


> not 100% sure thius is what you need
> But you say with criteria, and next value
> But not what you show
> 
> ...



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 Number14OA-20BK-8SIR14OA-20BK-8SIR14OA-20BK-8SIR14OA-24BK-8SIR14OA-24BK-8SIR14OA-24BK-8SIR10PI-20BK70PI-14XBK4RG10PI-20BK60PI-14BK4RG35SA-12BK4RG10PI-16BK60PI-14BK4RG35SA-12BK4RG10PI-16BK60PI-14BK4RG35SA-12BK4RG35SA-12BK4RG70PI-16XBK4RG60PI-14BK4RG70PI-16XBK4RG60PI-14BK4RG35SA-14BK4RG70PI-16XBK4RG60PI-14BK4RG

Model and Serial Numbers in inventory:

Item NumberSerial Number10CH-160104795410CH-160104795710CH-160099098910CH-160109991210CH-160104795510CH-160098330610CH-160104795610CH-16BKDT0113593610CH-16BKDT0109500510CH-16BKDT0114184110CH-180105940210CH-180104796110CH-180104796210CH-180104796310CH-180104795810CH-180101583710CH-180114118810CH-180098330910CH-180104795910CH-180106282210CH-180115717810CH-180104796010CH-18BKDT0112200110CH-18BKDT0116376610CH-18BKDT0114364810CH-18BKDT0114364910CH-18BKDT0114378510CH-18BKDT0114365010CH-18BKDT0112200010CH-18BKDT0114572810CH-18BKDT0114191310CH-18BKDT0115221210CH-18BKDT01151579


----------



## Sufiyan97 (Dec 19, 2022)

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:
> 
> ...



Please post some expected results from the above data set


----------



## brett781 (Dec 19, 2022)

Sufiyan97 said:


> Please post some expected results from the above data set


See below.  

Serial Numbers Needed:

Purchase OrderModel12310CH-20BKDT12410CH-20BKDT12510ET-16BK-MR12610ET-18BK-MR12710LX-12BK6SIR12810LX-12BK6SIR12910OA-18BK-8SIR13010OA-18BK-8SIR13110PI-16BK13210PI-16BK13310PI-16BK13410PI-16BK13510PI-20BK13610PI-20BK13710PI-20BK13810SR-12XLBK6SIR13910SR-12XLBK6SIR14010SR-12XLBK6SIR14110SR-12XLBK6SIR

Serial Numbers Available

ModelSerial10CH-20BKDT0117202610CH-20BKDT0117202710CH-20BKDT0117202510ET-16BK-MR0107278210ET-16BK-MR0102239410ET-16BK-MR0103552510ET-18BK-MR0103994810ET-18BK-MR0101890910ET-18BK-MR0107279010LX-12BK6SIR0112006010LX-12BK6SIR0103376610LX-12BK6SIR0103376710LX-12BK6SIR0103378010LX-12BK6SIR01033781

Expected Output:

Purchase OrderTrailer Item NumberSerial Number12310CH-20BKDT0117202612410CH-20BKDT0117202712510ET-16BK-MR0107278212610ET-18BK-MR0103994812710LX-12BK6SIR0112006012810LX-12BK6SIR01033766


----------



## Sufiyan97 (Dec 19, 2022)

Try

Book7ABCDEFG2Purchase OrderModelSerial NumberModelSerial312310CH-20BKDT117202610CH-20BKDT1172026412410CH-20BKDT117202710CH-20BKDT1172027512510ET-16BK-MR107278210CH-20BKDT1172025612610ET-18BK-MR103994810ET-16BK-MR1072782712710LX-12BK6SIR112006010ET-16BK-MR1022394812810LX-12BK6SIR103376610ET-16BK-MR1035525912910OA-18BK-8SIR 10ET-18BK-MR10399481013010OA-18BK-8SIR 10ET-18BK-MR10189091113110PI-16BK 10ET-18BK-MR10727901213210PI-16BK 10LX-12BK6SIR11200601313310PI-16BK 10LX-12BK6SIR10337661413410PI-16BK 10LX-12BK6SIR10337671513510PI-20BK 10LX-12BK6SIR10337801613610PI-20BK 10LX-12BK6SIR10337811713710PI-20BK 1813810SR-12XLBK6SIR 1913910SR-12XLBK6SIR 2014010SR-12XLBK6SIR 2114110SR-12XLBK6SIR 22Sheet1Cell FormulasRangeFormulaC3: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))),"")


----------



## brett781 (Dec 19, 2022)

Sufiyan97 said:


> Try
> 
> Book7ABCDEFG2Purchase OrderModelSerial NumberModelSerial312310CH-20BKDT117202610CH-20BKDT1172026412410CH-20BKDT117202710CH-20BKDT1172027512510ET-16BK-MR107278210CH-20BKDT1172025612610ET-18BK-MR103994810ET-16BK-MR1072782712710LX-12BK6SIR112006010ET-16BK-MR1022394812810LX-12BK6SIR103376610ET-16BK-MR1035525912910OA-18BK-8SIR 10ET-18BK-MR10399481013010OA-18BK-8SIR 10ET-18BK-MR10189091113110PI-16BK 10ET-18BK-MR10727901213210PI-16BK 10LX-12BK6SIR11200601313310PI-16BK 10LX-12BK6SIR10337661413410PI-16BK 10LX-12BK6SIR10337671513510PI-20BK 10LX-12BK6SIR10337801613610PI-20BK 10LX-12BK6SIR10337811713710PI-20BK 1813810SR-12XLBK6SIR 1913910SR-12XLBK6SIR 2014010SR-12XLBK6SIR 2114110SR-12XLBK6SIR 22Sheet1Cell FormulasRangeFormulaC3: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))),"")



Perfect.  Thank you!


----------



## Sufiyan97 (Dec 19, 2022)

You're Welcome.


----------

