# Return multiple columns for each row with Dynamic Array INDEX/MATCH



## btj88 (Dec 28, 2022)

Hi, I'm wondering what the best way is to return all columns (T and U) in the following setup:






I have tried using BYROW/LAMBDA to no avail. Perhaps there is a better function than INDEX/MATCH for this?


----------



## Fluff (Dec 28, 2022)

Hi & welcome to MrExcel.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What output are you looking for?


----------



## btj88 (Dec 28, 2022)

Thanks for the quick reply, Fluff. I've updated my account details; i'm using this Excel version:

Microsoft® Excel® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20188) 64-bit 

Here is output I'm looking for:


----------



## Fluff (Dec 28, 2022)

Ok, how  about
Fluff.xlsmABC12CatAC3DogBD456CatAC7DogBD8AntN/A#N/A9MainCell FormulasRangeFormulaB6:C8B6=DROP(REDUCE("",A6:A8,LAMBDA(a,b,VSTACK(a,FILTER(B2:C3,A2:A3=b,"N/A")))),1)Dynamic array formulas.


----------



## btj88 (Dec 28, 2022)

Thanks, Fluff! That seems to work--I need to work this into a more complex spot where I'll be summing different size arrays with different criteria. I'm surprised there isn't an easier way to do this with INDEX or even SUMIFS + MAP. In a nutshell I'm looking to do this, which I can get working with your formula suggestion. Curious if there is a cleaner way to go about it though.


----------



## Fluff (Dec 28, 2022)

If you are trying to sum the values, then why not just use sumifs.


----------

