Forum Discussion
VonJoe24
Aug 11, 2023Copper 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.
- 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)
5 Replies
Sort By
- mtarlerSilver Contributorif those ref IDs are unique it may be easier to use that column A:
=XLOOKUP("*"&a2:a100&"*", Raw!a2:a100, Raw!f2:f100,"",2)- VonJoe24Copper Contributor
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.
- mtarlerSilver Contributorah 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)