Two-way XLOOKUP to return the last non-blank value at a row–column intersection
Our take
Hi,
I’m trying to retrieve the value that corresponds to a specific product (column) and year (row). Each year is broken down into months, and some of those monthly cells are blank.
What I want is for the formula to return the latest available value within a given year for a selected product—in other words, the last non-empty cell for that product within the year.
I attempted to nest an XLOOKUP for the year inside another XLOOKUP for the product, using the search mode argument to search from right to left. While this works when applied to a single data row, it returns an error when I extend the formula across the full range of data rows.
My formula: =XLOOKUP(1, (C2:Z2=B12) * (A5:A8=A12) * (C5:Z8<>""), C5:Z8, ,0,-1)
Not sure what I missed. Would appreciate any help I can get please. Thank you.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience