How to search for size in the list and set costing based on it?

I need to search the ‘ITEM NAME’ list for matching size and set MRP based on it.

For Example-

NIKE PAHANTOM 38 RED”s MRP needs to be set 320

NIKE PAHANTOM 44 RED”s MRP needs to be set 400

>Solution :

Using XLOOKUP() would work:

=XLOOKUP(--INDEX(TEXTSPLIT(A8," "),3),$B$1:$E$1,$B$2:$E$2,"Not Found")

Or, if the pattern remains same then using HLOOKUP()

=HLOOKUP(--MID(A8,14,2),$B$1:$E$2,2,0)

Or, Using the same method with HLOOKUP() and TEXTSPLIT()

=HLOOKUP(--INDEX(TEXTSPLIT(A8," "),3),$B$1:$E$2,2,0)