Find J/N Value

[Roy_Excel_Island_Apps]

Hi all!

On the picture you'll see two tables. The table on the left is the lookup data table. In the table on the right, I'm searching for a formula to put in the J/N result column.
The formula needs to search for the code, eg. BK -> row 14 -> find the value on the same position in the J/N column (column B) -> J

I already managed to find the row where the code is (via Index/Match), but can't find the position in column B.

Please help!

Kind regards, Roy

 

[Fluff]

How about

=XLOOKUP(D2,TEXTSPLIT(TEXTJOIN(",",,$A$2:$A$14),","),TEXTSPLIT(TEXTJOIN(",",,$B$2:$B$14),","))