Get A Value From A Record (Lookup & Positional)

Input collection: myCars23

SerialNo	Year	Make	Model
6453	2016	Toyota	Corolla
8946	2014	Honda	Civic
3571	2019	Toyota	Camry
1165	2018	Honda	Civic
8973	2018	Hyundai	Elantra
4529	2020	Honda	Accord

Output value: mySolution23a (model of car with row with SerialNo 8973)

Elantra

Solution code:

//Create a collection
ClearCollect(myCars23,
{SerialNo:"6453", Year: 2016, Make: "Toyota", Model: "Corolla"},
{SerialNo:"8946",Year: 2014, Make: "Honda", Model: "Civic" },
{SerialNo:"3571",Year: 2019, Make: "Toyota", Model: "Camry" },
{SerialNo:"1165",Year: 2018, Make: "Honda",Model: "Civic"},
{SerialNo:"8973",Year: 2018, Make: "Hyundai", Model: "Elantra"},
{SerialNo:"4529",Year: 2020, Make: "Honda", Model: "Accord"}
);

//Get cell from 1st matching row
Set(
    mySolution23a,
    LookUp(myCars23,SerialNo="8973", Model)
);

Output value: mySolution23b (make of car on 4th row)

Honda

Solution code:

//Create a collection
ClearCollect(myCars23,
{SerialNo:"6453", Year: 2016, Make: "Toyota", Model: "Corolla"},
{SerialNo:"8946",Year: 2014, Make: "Honda", Model: "Civic" },
{SerialNo:"3571",Year: 2019, Make: "Toyota", Model: "Camry" },
{SerialNo:"1165",Year: 2018, Make: "Honda",Model: "Civic"},
{SerialNo:"8973",Year: 2018, Make: "Hyundai", Model: "Elantra"},
{SerialNo:"4529",Year: 2020, Make: "Honda", Model: "Accord"}
);

//Get cell from table position code
Set(
    mySolution23b,
    Last(FirstN(myCars23,4)).Make
);