Google Sheets formula to lookup value for all rows in another sheet by column names and returning va-CodePudding

I have a Sheet1 with data like this:

one	two	three	four
a	b	c	d
e	f	g	h
i	j	k	l
m	n	o	p

I have Sheet2 with data like this:

alpha	value 1
c
k
g
c

For each row in Sheet2, I want to look up Sheet2.A2:A in Sheet1 according to a lookup column name in Sheet1 and returning values from the associated row in Sheet1 by return column name in Sheet1.

So, a few examples:

Lookup Sheet2.A2:A in Sheet1.three and return Sheet1.one
Lookup Sheet2.A2:A in Sheet1.two and return Sheet1.four

The idea is the formula would specify the lookup column name and return column name and I'd just change it for each lookup I need to do.

Imagine the formula was something like:

=ARRAYFORMULA(
  SOMEFORMULA(
      A2:A,                          # lookup this value
      GETCOLUMN(Sheet1, "three"),    # in this column in Sheet1
      GETCOLUMN(Sheet1, "one")       # and return the value from this column in Sheet1
  )
)

So, the expected result is:

alpha	value
c	a
k	i
g	e
c	a

I can use the new Google Sheet formulas they just released -- except named ranges. I feel like there is some clever trick using them, but I can't come up with it.

CodePudding user response：

Give a try on below formula:

=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,INDEX(Sheet1!A:D,MATCH(x,INDEX(Sheet1!A:D,,MATCH("three",Sheet1!A1:D1,0)),0),MATCH("one",Sheet1!A1:D1,0))))

By XLOOKUP() function.

=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,XLOOKUP(x,FILTER(Sheet1!A2:D,Sheet1!A1:D1="three"),FILTER(Sheet1!A2:D,Sheet1!A1:D1="one"),"")))

Sample Sheet Link.

CodePudding user response：

Use HLOOKUP to the get the correct column in Sheet1 and XLOOKUP to get the corresponding column A value:

=ARRAYFORMULA(
  XLOOKUP(
    A2:INDEX(A2:A,COUNTA(A2:A)),
    HLOOKUP("three",Sheet1!A1:Z,SEQUENCE(ROWS(Sheet1!A1:Z)),0),
    Sheet1!A1:A
  )
)