Search code examples
google-sheetsgoogle-sheets-formula

Find last matching entry in row based on column header info

In my example sheet, a station ID is entered into range K9:9, and a serial number is automatically determined in the corresponding column in range K11:11 by finding the appropriate single letter that represents the station ID (identified on the Data sheet), and adding a sequential number after it.

current formula

This is the formula I am using to do this (entered into cell K11 and copied to all other cells in the row): =IFERROR(CONCATENATE(VLOOKUP(K$9,Data!$A$2:$B$7,2,FALSE),(TEXT(COUNTA(FILTER($K$9:K$9,LEFT($K$9:K$9,4)=LEFT(K$9,4))),"0"))),)

However, this is not a typical case of serialization where all numbers are unique. When the numbers reach a certain point I need to be able to interrupt the sequence and restart the count by replacing a formula with a new serial number. For example, if I type M1 in cell Q11, I need the next serial number for this station to be M2 instead of the M5 I'm currently getting in cell W11. I realize I'm currently getting M5 due to using COUNTA.

How would I best perform a reverse lookup horizontally based on the header in the row the formula is in?

goal

Solution

  • You may try:

    for column_K cell: =xlookup(K$9,Data!$A:$A,Data!$B:$B)&1

    column_L and beyond: =

    ifna(let(Σ,xlookup(L$9,Data!$A:$A,Data!$B:$B),
          Σ&regexextract(xlookup(Σ&"*",$K$11:K$11,$K$11:K$11,0,2,-1)&"","\d+")+1))

    enter image description here