3

I have a sheet that's just a list of names. It's being used as a reference for a few places, one of which transposes the list to use the names as column headers in another sheet (offset by 1).

I can get the column letter for the corresponding name through a combination of MATCH and ADDRESS: =ADDRESS(1, MATCH("some name", A1:A) + 1) where A1:A is the list of names.

This is all well and good, however I'd like to essentially loop through the row of names, do some calculations based on the aforementioned sheet with these names as column headers, and then filter out to a specific result. My problem however is that MATCH (nor XMATCH) seem to work within BYROW. The base for what I'm doing would be:
=BYROW(A1:A, LAMBDA(row, { INDEX(row, 1, 1), MATCH(INDEX(row, 1, 1), A1:A) + 1 }))

Problem is, it throws an error, specifically: "Result was not expanded automatically, please insert more rows (13)."

Just using the LAMBDA() alone works correctly having used the following to test:
=LAMBDA(row, { INDEX(row, 1, 1), MATCH(INDEX(row, 1, 1), A1:A) + 1 })(A1)

Why would MATCH/XMATCH not work within BYROW? Is there a way around this?

Using just the LAMBDA() function, I know all my formulae will work, assuming I can get BYROW() to work.

1 Answer 1

4

The match() function works fine in a lambda. Your formula errors out because it is producing more results than fit in the sheet. To make it work, weed out blank values, like this:

=byrow( 
  filter(A1:A, len(A1:A)), 
  lambda( 
    value, 
    { value, match(value, A1:A, 0) + 1 } 
  ) 
)

In the event the values in column A1:A are unique, a simpler way to do the same is to just use filter():

=filter( { A1:A, row(A1:A) + 1 }, len(A1:A) )

Always specify the 0 with match(), unless your data is sorted (and even then it is usually a good idea.)

1
  • 1
    I had considered just using a filter but unfortunately that won't work in my case, or rather it will but I'd eventually need to wrap it in a BYROW eventually for some other stuff. Other than that, you have once again helped me and my mush brain out, so thanks a lot :)
    – Spedwards
    Commented Dec 8, 2022 at 14:42

Your Answer

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

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