Forum Discussion
Lookup seat number on table plan
The setup isn't ideal for any kind of regular lookup function, but provided your Excel supports TEXTJOIN, the attached workbook contains a solution that works.
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