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 (column A) with corresponding numerical data, eg test scores (column B). I'm trying to create something where I can list the scores from 1-100, and the table will pull the names next to the relevant score so that individuals from different groups can be easily compared. The issue is that multiple people have the same test score.
Can I get a VLOOKUP to return more than one column B value for the same column A value? I've also tried formatting by data as text and using "TEXTJOIN" following the steps on the link below but can't get it to work and am struggling to replicate their example, I can only get it to return all of the values in one cell instead of splitting by name.
Any advice?
Thanks
=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),"")
