SOLVED

# Help with excel function/formula

Copper Contributor

# Help with excel function/formula

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.

5 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Help with excel function/formula

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)

# Re: Help with excel function/formula

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

# Re: Help with excel function/formula

@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.

# Re: Help with excel function/formula

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)

# Re: Help with excel function/formula

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!
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Help with excel function/formula

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)