0

if I could use XLOOKUP it would simplify my formulas, but it doesn't seem to offer a way to return a relative column reference like VLOOKUP. For example, with VLOOKUP I had:

=VLOOKUP(I21,INDIRECT($Q$9),4,FALSE)
  • I21: Text to search for
  • INDIRECT($Q$9): Cell containing the named range (I have several tables of data, the user can select which table with a drop-down)
  • 4: Relative number of columns over from the searched text in the selected table

XLOOKUP bills itself as being able to totally replace VLOOKUP, but I don't see how ask it for a relative reference which is the default behavior for VLOOKUP.

Hopefully this example is clear, let me know if not.

catdotgif
  • 1,738
  • 3
  • 15
  • 13

1 Answers1

1

You can still use relative column references, but it might make your formulas more complicated. You have to use the INDEX function:

=XLOOKUP(I21,INDEX(INDIRECT($Q$9),,1),INDEX(INDIRECT($Q$9),,4))

The first INDEX function delivers the fist column (your search range) to XLOOKUP, the second INDEX function tells the XLOOKUP function to look up your result in the forth column.

Is that what you are looking for?

Michael Wycisk
  • 1,590
  • 10
  • 24