Search code examples
excelexcel-formulalookup

How to I get excel to look up values in the nth column based on a number in a cell?

I'm trying to make an inventory spreadsheet that calculates a bunch of metrics, all based on summing amounts of stock across multiple sites. I was using:

=XLOOKUP(
  [@[Product name]], 
  Ontario!$B:$B,  
  IF($C$2=1, Ontario!$E:$E, 
    IF($C$2=2, Ontario!$F:$F, Ontario!$G:$G)
  ), 
  0
)

when we calculated this quarterly, with C2 being the month of the quarter (1, 2, or 3)

There has to be a better way than just quadrupling the number of IF() statements, something like a reverse COLUMN() function? I'm not a programmer and just use excel to make my job easier, not harder.

Solution

  • You could try using one of the following:

    =XLOOKUP([@[Product name]], Ontario!$B:$B, CHOOSECOLS(Ontario!$E:$G,$C$2),0)

    or,

    =XLOOKUP([@[Product name]], Ontario!$B:$B, CHOOSE($C$2, Ontario!$E:$E, Ontario!$F:$F, Ontario!$G:$G),0)

    However, one can also use VLOOKUP() here as it seems:

    =VLOOKUP([@[Product name]],Ontario!$B:$G,C$2+3,0)