I have the below formula I am trying to get to work.
"=XLOOKUP(1,MMULT(--(R1C41:R168C54=RC[-1]),TRANSPOSE(COLUMN(C[-16]:C[-3])^0)),R1C40:R168C40,"""")"
As above it works, however I am trying to make it relative due to changing nature of input.
The bits I have already are: a dim as integer called 'offcol' which is varied depending on input. a dim as integer called 'last row' which is varied depending on input.
I want to make the formula look like below "=XLOOKUP(1,MMULT(--(R1C41:R168C54=RC[-1]) "=XLOOKUP(1,MMULT(--(R1C[-offcol -2]:RlastrowC[-3]=RC[-1])
TRANSPOSE(COLUMN(C[-offcol-2]:C[-3])^0))
,R1C[-offcol-1]:RlastrowC[-offcol-1],"""")
You need to concatenate the variables into the formula string:
"=XLOOKUP(1,MMULT(--(R1C[-" & offcol -2 & "]:R" & lastrow & "C[-3]=RC[-1]),TRANSPOSE(COLUMN(C[-" & offcol-2 & "]:C[-3])^0)),R1C[-" & offcol-1 & "]:R" & lastrow & "C[-" & offcol-1 & "],"""")"