Forum Discussion

Marooned's avatar
Marooned
Copper Contributor
May 16, 2025

Lookup seat number on table plan

Hello all, hope you can help me.

In Excel, I've a table plan where I've assigned names to numbered seats.

I now want to read the 'read' that table plan and create a list that tells me the name and seat number.

I already have the list of names (in Column L), but need a formula for Column M to do the lookup of the seat number on the table plan.  The seat number is always located in the cell above the name.

For cell M2, the formula should return that Arthur is in seat C7, and in M3, that Ben is in seat B3, and so on.

I suspect the formula could be a combination of one of the Lookup variations, INDEX or MATCH, or even using the CELL command, but I've not fathomed it yet.

Does anyone have any suggestions on how I could do it?

Many thanks

 

 

Formulas and Functions

3 Replies

Sort By
  • Marooned's avatar
    Marooned
    Copper Contributor
    May 17, 2025

    Hi Riny,

    Update - I found that 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!

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 17, 2025

    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.

    • Marooned's avatar
      Marooned
      Copper Contributor
      May 17, 2025

      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

Resources

OSZAR »