Thread: VLookup

Hi there everybody

I am hoping somebody can help me with this one. I have pondered it for a while and am sure excel has a function to do it. See attached file: example_table.xlsx

What I need is a formula in C19, D19, E19, etc,,, that looks at table A and puts in the cells the zone name in which the alias appears in. It must find every zone the alias appears in and put each one in a new column, after the previous one, as shown in Table B.

Is there a formula which can do this? N.B table B is actually on a different worksheet within the same book.

Thanks in advance!

In cell C19 try:

=IF(COLUMN()-2>COUNTIF($B$3:$E$14,$B19),"",INDEX($A:$A,LARGE(IF($B$3:$E$14=$B19,ROW($B$3:$E$14),0),COUNTIF($B$3:$ E$14,$B19)-(COLUMN()-3))))

This is an array formula and must be entered using Ctrl-Shift-Enter, not just Enter. Once entered you can copy it across and down in the normal way.

This is the correct workboook

No problem. In cell K2 of the port allocation sheet put the formula:

=IF(COLUMN()-10>COUNTIF(ZONES_RELN1!$B$1:$O$1000,$C2),"",INDEX(ZONES_RELN1!$A:$A,LARGE(IF(ZONES_RELN1!$B$1:$O$100 0=$C2,ROW(ZONES_RELN1!$B$1:$O$1000),0),COUNTIF(ZONES_RELN1!$B$1:$O$1000,$C2)-(COLUMN()-11))))

It's still an array formula, so Ctrl-Shift-Enter to enter it.