Need help with formula. Do I use XLOOKUP? VLOOKUP? Something else?

Question

I need help with this Google Sheet: https://docs.google.com/spreadsheets/d/1tL3rJlx-SupBoX3AtOTzpePQigA-EB_E_hv7i5kAd7U/edit?usp=sharing

The Master Report sheet is pulling data from the other worksheets. I need columns N,O,P,Q to query columns D,F,H,J in the same row. If the data in those fields match the data in N1, O1, P1, or Q1, I would like it to input the data from the column to the right.

For example, N2 queries D2,F2,H2,J2 to match N1. If true, N2 should return the data from E2,G2,I2 or K2.

In this spreadsheet, the formula appears to work N2 only. But I can't get it to work in any other fields. Am I using the wrong formula?

Answer 1

You can use XLOOKUP with the ranges "moved" one column, in order to emulate that "offset" you have in your values, like this:

=XLOOKUP(N$1,$D2:$J2,$E2:$K2,,0)

You can drag or copy and paste in your whole range. Or you could use this formula in N2 and it will expand:

=MAKEARRAY(COUNTA(L2:L)-1,COUNTA(N1:1),lambda(r,c,lambda(range,headers,IFERROR(index(range,r,MATCH(index(headers,,c),index(range,r,),0)+1),""))(D2:K,N1:1)))