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

[btj88]

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]

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]

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]

Ok, how about

Fluff.xlsm
	A	B	C
1
2	Cat	A	C
3	Dog	B	D
4
5
6	Cat	A	C
7	Dog	B	D
8	Ant	N/A	#N/A
9
Main

Cell Formulas
Range	Formula
B6:C8	B6	=DROP(REDUCE("",A6:A8,LAMBDA(a,b,VSTACK(a,FILTER(B2:C3,A2:A3=b,"N/A")))),1)
Dynamic array formulas.

 

[btj88]

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]

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