MATCH two ranges, column by column

narnian_uk

New Member
Joined
Jul 28, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHIJKLMNOPQ
1Header 1Header 2Header 3Header 4Header 5Header 1Header 2Header 3Header 4Header 5
2afinrafinr#N/A#N/A#N/A#N/A#N/A
3cgjnsbgjos#N/A#N/A#N/A#N/A#N/A
4dhiotchkpt#N/A#N/A#N/A#N/A#N/A
5ahkpudlqu#N/A#N/A#N/A#N/A#N/A
6eflouemv#N/A#N/A#N/A#N/A#N/A
7bgmqv#N/A#N/A#N/A#N/A#N/A
Sheet3
Cell Formulas
RangeFormula
M2:Q7M2=LET(range1, A2:E7, range2, G2:K6, cols, SEQUENCE(,COLUMNS(range1)), MATCH(CHOOSECOLS(range1, cols), CHOOSECOLS(range2, cols)))
Dynamic array formulas.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
Is this what you want?
Fluff.xlsm
ABCDEFGHIJKLMNOPQ
1Header 1Header 2Header 3Header 4Header 5Header 1Header 2Header 3Header 4Header 5
2afinrafinr11111
3cgjnsbgjos32212
4dhiotchkpt43123
5ahkpudlqu13334
6eflouemv51424
7bgmqv22545
Data
Cell Formulas
RangeFormula
M2:Q7M2=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.
 
Upvote 0
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:

Excel Formula:
=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

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

in cell AP2 and then use

Excel Formula:
=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?
 
Upvote 0
Did you get a circular reference error warning?
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top