Morning all,
I have a large list of numbers sorted into 4 columns.
I am trying to match the 1st 4 columns (dynamic list) with a 4 column static list of possible permutations.
If the formula finds a match it should return a predetermined value for that particular combination of numbers.
there is plenty of info on how to do this with 2 columns but I can't get my head around what is required to match 4 columns.
The dynamic list is organised this way, For 50,000 rows (so far!)
A B C D
1ST 2ND 3RD 4TH
2 3 6 1
8 1 2 12
1 11 5 6
4 10 1 8
1 4 6 7
2 7 11 8
7 1 2 3
1 7 6 3
2 11 4 1
I have another range that covers all possible permutations of the 1st four for #'s 1 -12
INDEX # #1 #2 #3 #4
1 1 2 3 4
2 1 2 3 5
3 1 2 3 6
4 1 2 3 7
5 1 2 3 8
6 1 2 3 9
7 1 2 3 10
8 1 2 3 11
9 1 2 3 12
etc,etc until...
11875 12 11 10 4
11876 12 11 10 5
11877 12 11 10 6
11878 12 11 10 7
11879 12 11 10 8
11880 12 11 10 9
If a match is found for columns a,b,c,d I would like to be able to return the index #1-11880 for that row.
Attached is a sample of what I am trying to achieve.
Cheers
Martin
Bookmarks