Help with excel function/formula

[VonJoe24]

 I need help to look up the Completion date (Coaching Raw sheet) based on the reference ID. The raw sheet contains 3 columns where we can find the possible reference ID with its corresponding completion date. In other words, I am trying to look up a value of a cell to a different sheet on three different columns and return value (completion date). Thank you in advance.

[#2 · Accepted solution]

if those ref IDs are unique it may be easier to use that column A:
=XLOOKUP("*"&a2:a100&"*", Raw!a2:a100, Raw!f2:f100,"",2)

[#3]

@mtarler it works! Thank you! Appreciate the help!

[#4]

@mtarler Hi just a follow up question. The formula is working but not on single entered reference ID. Do you happen to know how to fix this? The sample screenshot shows reference ID 8018 is completed but date not showing on the cell with formula. Formula is only working for those multiple reference IDs in one cell. 

[#5]

ah yes, sorry. That 8018 is identified as a NUMBER but the equation is turning the lookup values into text (adding the "*" before and after) and hence the text is not the same as a NUMBER. Try this:
=XLOOKUP("*"&a2:a100&"*", TEXT(Raw!a2:a100,"0"), Raw!f2:f100,"",2)

[#6]

Thank you it works. I did not include a single entereed ref id on my previous sample. It is only now that I realized when I am working on the file already. Thank you so much again!