narnian_uk
New Member
- Joined
- Jul 28, 2021
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
- 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.
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. |