0

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.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • 1
    Please make a representative sample showing different results. Preferably as a new question. As this question already has a answer based on your old question. – TheMaster Oct 01 '22 at 14:57

1 Answers1

1

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")))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • What is `A2:INDEX(A2:A,COUNTA(A2:A))`? I've never seen that kind of structure. – IMTheNachoMan Oct 01 '22 at 14:52
  • 2
    @IMTheNachoMan https://stackoverflow.com/a/46884012/ – TheMaster Oct 01 '22 at 14:53
  • I realized I forgot to include something in my question. I edited it. You hardcoded `Sheet1!C2:C` but I need to be able to look up columns by column name. – IMTheNachoMan Oct 01 '22 at 14:54
  • @IMTheNachoMan Then try index/match instead xlookup `=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,INDEX(Sheet1!C:D,MATCH(x,Sheet1!C:C,0),MATCH("three",Sheet1!C1:D1,0))))`. – Harun24hr Oct 01 '22 at 15:06