Forum Discussion
Solved
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 complet...
- 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)
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.
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)
=XLOOKUP("*"&a2:a100&"*", TEXT(Raw!a2:a100,"0"), Raw!f2:f100,"",2)
- 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!