0

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],"""")

Zeetek
  • 1
  • 1

1 Answers1

0

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 & "],"""")"
Rory
  • 32,730
  • 5
  • 32
  • 35