Forum Discussion
Lookup to return multiple values in one cell
Hi, I'm far from an excel whizz but I try my best! (I'm also working in Google sheets if that makes any difference, but I can move to excel if easier) Essentially, I have different lists of names...
=TEXTJOIN(", ",,FILTER($A$2:$A$19,$B$2:$B$19=E2))Maybe with this formula if you can apply the FILTER function.
=TEXTJOIN(", ",,IF($B$2:$B$19=E2,$A$2:$A$19,""))This formula works as well in my Excel online sheet.
- This is exactly what I am after!
For some reason my version of excel does not recognise the "TEXTJOIN" function, so I'm giving it a go on Google Sheets instead. I've tried both of your formulae but both are still returning errors...I get a "NOMATCH FOUND" error with the filter formula and the one with the IF statement just returns blank cells.What is your version of Excel? TEXTJOIN is available since Excel 2019. It doesn't work in Excel 2013 for example. FILTER requires the newest version of Excel.
The attached file is Excel 2013 and it shows a possible way with VLOOKUP. You have to enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=IFERROR(VLOOKUP($E2&F$1,CHOOSE({1,2},$B$2:$B$24&$C$2:$C$24,$A$2:$A$24),2,0),"")- Thank you so much for your help!
I think I'm finally getting somewhere with Google Sheets - I think the problem is that the numerical values are not being seen as text so the TEXTJOIN is returning errors. I'm trying to fix that now with some success so hoping I will be there soon. Otherwise I'll be hunting down a machine with a newer version of excel!
Thanks again 🙂
