Lookup a value in a list, if not found use previous value-CodePudding

We have a list with rooms and guest living in the rooms. We want to find the room number for each person. The room number is only filled in for the first person in the room, because it is better for the layout.

How can I find the room number in spite of there is no room number to the left of some people?

My idea was to just take the latest number in the list from that row where the name was found.

I stack all 3 lists into one with a Vstack and valuetotext (because these are Datatypes, which otherwise might not be recognized as text). But then I don't know how to do it. My tought was with a LOOKUP function which can find the last entry. But it is not working. I tried this: =LOOKUP(2;1/(INDEX($AA$7#;AE7;1)<>"");INDEX($AA$7#;AE7;1)) (ranges in this formula doesn't match with the picture, just to give you an idea)

I would prefer non Lambda formulas, because I'm not familar with them. If not possible otherwise, I can use Lambda as well.

CodePudding user response：

Perhaps, this is what you could have tried (I tried to recreate the data you are using except the data types):

• Formula used in cell U4

=LET(_data,VSTACK(J4:K35,N4:O35,R4:S35),
_rooms,TAKE(_data,,1),
_combinedData,HSTACK(SCAN(_rooms,_rooms,LAMBDA(x,y,IF(y=0,x,y))),TAKE(_data,,-1)),
SORT(FILTER(_combinedData,TAKE(_combinedData,,-1)<>0),,,1))

If you wish to have the header as well using the formula:

• Formula used in cell U4

=LET(_data,VSTACK(J4:K35,N4:O35,R4:S35),
_rooms,TAKE(_data,,1),
_combinedData,HSTACK(TAKE(_data,,-1),SCAN(_rooms,_rooms,LAMBDA(x,y,IF(y=0,x,y)))),
VSTACK({"Name","Room Number"},FILTER(_combinedData,TAKE(_combinedData,,1)<>0)))

Or, If I go by the approach, you were trying then :

• Formula used in cell X10

=VSTACK(J4:K35,N4:O35,R4:S35)

• Formula used in cell V19

=IF(U19="","",INDEX(LOOKUP(ROW($X$10:$X$105),ROW($X$10:$X$105)/($X$10:$X$105<>0),$X$10:$X$105),MATCH(U19,$Y$10:$Y$105,0)))