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 |
|---|---|
| c | |
| k | |
| g | |
| c |
For each row in Sheet2, I want to look up Sheet2.alpha in Sheet1.three and return the value of Sheet1.one. I want to do this by putting an array formula in B2.
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.
Edit: In this example I am checking against Sheet1.three and returning Sheet1.one but I need it to be flexible in that I can lookup and return against any column based on the column heading.
CodePudding user response：
BYROW() and XLOOKUP() are your friend in this case.
=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,XLOOKUP(x,Sheet1!C2:C,Sheet1!A2:A,"Not Found")))
