MATCH two ranges, column by column

[narnian_uk]

I have two ranges of data (in the example below, A1:E7 and G1:K6). Each range always has the same number of columns. I'm trying to write one spilling formula which will MATCH the contents of range 1, column 1 against the contents of range 2, column 1 - then the contents of range 1, column 2 against the contents of range 2, column 2, and so on (see M2). I can work out how to write a separate formula for each individual column, but I was hoping to avoid that.

Any help gratefully received

I should add that I realize that using LET in the formula below adds very little; I've only done it in this instance to try to make it more obvious what I'm trying to do.

AT Feedback.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Header 1	Header 2	Header 3	Header 4	Header 5		Header 1	Header 2	Header 3	Header 4	Header 5
2	a	f	i	n	r		a	f	i	n	r		#N/A	#N/A	#N/A	#N/A	#N/A
3	c	g	j	n	s		b	g	j	o	s		#N/A	#N/A	#N/A	#N/A	#N/A
4	d	h	i	o	t		c	h	k	p	t		#N/A	#N/A	#N/A	#N/A	#N/A
5	a	h	k	p	u		d		l	q	u		#N/A	#N/A	#N/A	#N/A	#N/A
6	e	f	l	o	u		e		m		v		#N/A	#N/A	#N/A	#N/A	#N/A
7	b	g	m	q	v								#N/A	#N/A	#N/A	#N/A	#N/A
Sheet3

Cell Formulas
Range	Formula
M2:Q7	M2	=LET(range1, A2:E7, range2, G2:K6, cols, SEQUENCE(,COLUMNS(range1)), MATCH(CHOOSECOLS(range1, cols), CHOOSECOLS(range2, cols)))
Dynamic array formulas.

 

[Peter_SSs]

Instead of a formula that is not doing what you want, can you manually fill in the results that you do want in those #N/A cells and post the data again? Add any further clarification about how you come up with those results if you can.

 

[Fluff]

Is this what you want?

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Header 1	Header 2	Header 3	Header 4	Header 5		Header 1	Header 2	Header 3	Header 4	Header 5
2	a	f	i	n	r		a	f	i	n	r		1	1	1	1	1
3	c	g	j	n	s		b	g	j	o	s		3	2	2	1	2
4	d	h	i	o	t		c	h	k	p	t		4	3	1	2	3
5	a	h	k	p	u		d		l	q	u		1	3	3	3	4
6	e	f	l	o	u		e		m		v		5	1	4	2	4
7	b	g	m	q	v								2	2	5	4	5
Data

Cell Formulas
Range	Formula
M2:Q7	M2	=LET(a,A2:E7,b,G2:K6,MAKEARRAY(ROWS(a),COLUMNS(a),LAMBDA(r,c,XMATCH(INDEX(a,r,c),INDEX(b,,c)))))
Dynamic array formulas.

 

[narnian_uk]

Is this what you want?

This certainly works - thank you. Out of interest: obviously, my data sets aren't just a, b, c, etc. My 'a' is really G2:N1541 (but the number of rows may vary). My 'b' is of a variable width and height. I therefore tried reformulating your formula as follows:

=LET(data, FILTER(G2:N10000, G2:G10000<>""), questions, INDEX(Questions!B1:Z100, SEQUENCE(COUNTA(Questions!A2:A100)), SEQUENCE(,COUNTA(Questions!B1:Z1))),MAKEARRAY(ROWS(data),COLUMNS(data),LAMBDA(r,c,XMATCH(INDEX(data,r,c),INDEX(questions,,c))-1)))

The formula stopped working (which is to say that initially Excel does nothing at all, and then, when I press a key or click the mouse button, the formula just returns 0). If, however, I put

=FILTER(G2:N10000, G2:G10000<>"")

in cell AP2 and then use

=LET(data, AP2#, questions, INDEX(Questions!B1:Z100, SEQUENCE(COUNTA(Questions!A2:A100)), SEQUENCE(,COUNTA(Questions!B1:Z1))),MAKEARRAY(ROWS(data),COLUMNS(data),LAMBDA(r,c,XMATCH(INDEX(data,r,c),INDEX(questions,,c))-1)))

then it works just fine. It doesn't really matter, but do you have any idea why this might be happening?

 

[Fluff]

Did you get a circular reference error warning?