Return multiple cell data with a VLOOKUP or INDEX MATCH

Lara Homewood

Hi, I am trying to return the text within multiple cells based on criteria of an adjacent cell. In this example I want to return JOINED cell information from any the [News / Key Information / Highlights] column cells that matches 'NEMEA" in the [Region / Dept] column ( Ialso want to add in the criteria of being in the future but perhaps that's another separate question!). I've tried using INDEX MATCH and a VLOOKUP but it only returns the first cell of matching data. Can anyone help please?

=IFERROR("NEMEA" + CHAR(10) + INDEX([News / Key Activities / Highlights]:[News / Key Activities / Highlights], MATCH("NEMEA", [Region / Dept]:[Region / Dept], 0)), "")

=IFERROR(VLOOKUP("NEMEA", [Region / Dept]:AOB, 1), 0) + CHAR(10) + IFERROR(VLOOKUP("NEMEA", [Region / Dept]:AOB, 2), 0)

David Joyeuse

Hi @Lara Homewood

If you want to return the content of more than one cells you have to use the JOIN/COLLECT function instead of INDEX/MATCH.

It would read like this:

=IFERROR("NEMEA" + CHAR(10) + JOIN(COLLECT([News / Key Activities / Highlights]:[News / Key Activities / Highlights], [Region / Dept]:[Region / Dept], "NEMEA"), " "),"")

Hope it helped

David Joyeuse

That would be this then:

=IFERROR("NEMEA" + CHAR(10) + JOIN(COLLECT([News / Key Activities / Highlights]:[News / Key Activities / Highlights], [Region / Dept]:[Region / Dept], "NEMEA"), CHAR(10)),"")

That should be working. I'm not sure how the JOIN function would treat the CHAR(10) though.

If that doesn't work, create an helper cell with some carriage return within it, and like to this cell in the JOIN function.

David Joyeuse

Hi @Lara Homewood

If you want to return the content of more than one cells you have to use the JOIN/COLLECT function instead of INDEX/MATCH.

It would read like this:

=IFERROR("NEMEA" + CHAR(10) + JOIN(COLLECT([News / Key Activities / Highlights]:[News / Key Activities / Highlights], [Region / Dept]:[Region / Dept], "NEMEA"), " "),"")

Hope it helped

Lara Homewood

That's amazing and works thank you!!! Silly question but is there a way to add +CHAR(10) between the Joins so there in a paragraph spacing between the joint cell info?

David Joyeuse

That would be this then:

=IFERROR("NEMEA" + CHAR(10) + JOIN(COLLECT([News / Key Activities / Highlights]:[News / Key Activities / Highlights], [Region / Dept]:[Region / Dept], "NEMEA"), CHAR(10)),"")

That should be working. I'm not sure how the JOIN function would treat the CHAR(10) though.

If that doesn't work, create an helper cell with some carriage return within it, and like to this cell in the JOIN function.

Lara Homewood

YOU ARE WONDERFUL!! THANK YOU SO MUCH!