XLookup Question

[PaulFogel123]

Hello,

I have what I would think would be an easy solution, but I can't figure it out. Can you help?

On the attached file, I would like a formula in column C that will lookup the department name in column B and return the appropriate value from column G, it being August. Next month, I will update the current month to be September in cell C4. I want the change in cell C4 to trigger the formula to return the correct value for the month selected.

The old VLookup might work here by specifying the column number to use with an IF formula. But I thought XLookup would work somehow.

Paul

 

[John Jairo V]

Hi, @PaulFogel123!

If Dept. Name is changing his order compare to Dept Name table lookup, then you can use this formula:

=XLOOKUP(B6:B58,E6:E58,INDEX(F6:Q58,,MATCH(C4,F5:Q5,)))

But if it's the same order, you can use:

=INDEX(F6:Q58,,MATCH(C4,F5:Q5,))

Another option could be:

=FILTER(F6:Q58,IFERROR(F5:Q5=C4,))

Blessings!

 

[Peter Bartholomew]

As you have it, you do not need to look up the Dept Name because the variance table is already in the correct order. An entire column is returned by

= XLOOKUP(monthCur, monthHdr, variance)

If the list of departments do not match the row headers then a second nested lookup can be used to return a partial column

= XLOOKUP(
    Dept,
    DeptHdr,
    XLOOKUP(
      monthCur,
      monthHdr,
      variance
    )
  )

Whilst I think of it, if you only seek the result for a single department then it is possible to lookup a row and a columns from the variance table and then use range intersection to return the reference to a single cell.

= XLOOKUP( Dept, DeptHdr, variance )
  XLOOKUP(monthCur, monthHdr, variance)

 

[PaulFogel123]

Hi, @PaulFogel123!

If Dept. Name is changing his order compare to Dept Name table lookup, then you can use this formula:

=XLOOKUP(B6:B58,E6:E58,INDEX(F6:Q58,,MATCH(C4,F5:Q5,)))

But if it's the same order, you can use:

=INDEX(F6:Q58,,MATCH(C4,F5:Q5,))

Another option could be:

=FILTER(F6:Q58,IFERROR(F5:Q5=C4,))

Blessings!

Hi John,
I think this formula,
XLOOKUP(B6:B58,E6:E58,INDEX(F6:Q58,,MATCH(C4,F5:Q5,)))
will be more flexible, just in case I change the list order in one of the tables.
Thank you.