Excel 2016: Vlookup – Return all entries in a column using vLookup joined by comma (NO TextJoin)

In Consejo rápido by RicardoLeave a Comment


Windows – Excel 2016


This is our data ⬇️

Data is formated as an structured Table (Excel Table)

Problem / Need:

Write in one cell all the matches in the “Cargo” column where “Área” is “Servicios generales”


Formula → =CONCAT(IF(Tabla[Área]="Servicios generales",Tabla[Cargo]&",",""))


Tabla[Área]="Servicios generales"

This part of the formula evaluates each cell in the Area column and returns true for the ones that match the text


This part of the formula returns the Cargo in the row of each of the matched cells and adds the comma at the end of each one

Note: The comma at the end is the key to replace the TextJoin formula that is not available until Excel version 2019 onwards


The Concat function takes each result and writes them in the cell


Leave a Comment