Lookup seat number on table plan

Hi Riny,

Looks like my reply yesterday never saved.

Many thanks, I would never have thought of that formula, you've saved me lots of time!

Initially I found that the formula gave me a "#Value!" message, but by clicking on the formula in the formula bar, and pressing CTL-SHIFT-ENTER, the correct value would appear.  I could then copy this all the way down the list.  Do you know if there's any automatic way of doing it?  If not, it's something I can live with, as the rest of it is a massive improvement.

Thanks again!

Hi Riny,

That's amazing, thanks, I'd have never thought of that!

I've almost got it working on my spreadsheet...
I downloaded your spreadsheet and it works perfectly, but when I copy/paste the formula into column M on my spreadsheet, I get a #VALUE! return.  The formula appears as:

=TEXTJOIN("",,IF(--($B$6:$J$19=L22),$B$5:$J$18,""))

Error checking tells me the formula is of the wrong data type, so I tried a few options to update this using Data>Text to Columns, but I've not been successful in fixing it yet.

When I copy/paste all of your data (rather than only the formulas in column M) into my spreadsheet below my data it also works.

What I noticed was that when I click on the cell with the formula in column M that I copied from your data, the expression appears in the formula bar with curly brackets around it:

{TEXTJOIN("",,IF(--($B$6:$J$19=L22),$B$5:$J$18,""))}

but when I then click on it there, it changes to 

=TEXTJOIN("",,IF(--($B$6:$J$19=L22),$B$5:$J$18,""))

and at the same time the adjacent left cell in column L is highlighted, plus the two ranges defined in the expression and the word ADDRESS appears in the Name box.

Do you have any suggestion on how I can fix this?  I feel it's so close to working!

Many thanks, Marooned