0

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?

1 Answer 1

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)))
1
  • Thank you, @MartÍn ! Nov 30, 2022 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.