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

Question

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

Answer 1

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)

---