I would like to use index and match with multiple columns. For example, using the data below, I want to index column B by searching for the values in column D in the array F1:H12.
So, index(B1:B12, match(D1, F1:H12, 0)) would return April.
I realise that match() does not work with multiple columns. Is there some way of modifying the function to deal with this issue?
Any help would be greatly appreciated!
<tbody>
</tbody>
So, index(B1:B12, match(D1, F1:H12, 0)) would return April.
I realise that match() does not work with multiple columns. Is there some way of modifying the function to deal with this issue?
Any help would be greatly appreciated!
A | B | C | D | E | F | G | H | I |
1 | January | CLB-FC-2D-ST-SPA-4001-01 | CLB-FC-2D-ST-SPA-4101-01 | CLB-FC-2D-ST-SPA-4102-01 | CLB-FC-2D-ST-SPA-4101-02 | =index(B1:B12, match(D1, F1:H12, 0) | ||
2 | February | CLB-FC-2D-ST-SPA-4002-01 | CLB-FC-2D-ST-SPA-4201-01 | CLB-FC-2D-ST-SPA-4202-01 | CLB-FC-2D-ST-SPA-4201-02 | =index(B1:B12, match(D2, F1:H12, 0) | ||
3 | March | CLB-FC-2D-ST-SPA-4003-01 | CLB-FC-2D-ST-SPA-4301-01 | CLB-FC-2D-ST-SPA-4302-01 | CLB-FC-2D-ST-SPA-4301-02 | |||
4 | April | CLB-FC-2D-ST-SPA-4004-01 | CLB-FC-2D-ST-SPA-4001-01 | CLB-FC-2D-ST-SPA-4002-01 | CLB-FC-2D-ST-SPA-4001-02 | |||
5 | May | CLB-FC-2D-ST-SPA-4005-01 | CLB-FC-2D-ST-SPA-4003-01 | CLB-FC-2D-ST-SPA-4004-01 | CLB-FC-2D-ST-SPA-4003-02 | |||
6 | June | CLB-FC-2D-ST-SPA-4101-02 | CLB-FC-2D-ST-SPA-4005-01 | CLB-FC-2D-ST-SPA-4102-02 | CLB-FC-2D-ST-SPA-4005-02 | |||
7 | July | CLB-FC-2D-ST-SPA-4007-01 | CLB-FC-2D-ST-SPA-4006-01 | CLB-FC-2D-ST-SPA-4202-02 | CLB-FC-2D-ST-SPA-4006-02 | |||
8 | August | CLB-FC-2D-ST-SPA-4008-01 | CLB-FC-2D-ST-SPA-4007-01 | CLB-FC-2D-ST-SPA-4302-02 | CLB-FC-2D-ST-SPA-4007-02 | |||
9 | September | CLB-FC-2D-ST-SPA-4011-02 | CLB-FC-2D-ST-SPA-4008-01 | CLB-FC-2D-ST-SPA-4002-02 | CLB-FC-2D-ST-SPA-4008-02 | |||
10 | October | CLB-FC-2D-ST-SPA-4010-01 | CLB-FC-2D-ST-SPA-4009-01 | CLB-FC-2D-ST-SPA-4004-02 | CLB-FC-2D-ST-SPA-4009-02 | |||
11 | November | CLB-FC-2D-ST-SPA-4001-02 | CLB-FC-2D-ST-SPA-4010-01 | CLB-FC-2D-ST-SPA-4005-03 | CLB-FC-2D-ST-SPA-4010-02 | |||
12 | December | CLB-FC-2D-ST-SPA-4102-01 | CLB-FC-2D-ST-SPA-4011-01 | CLB-FC-2D-ST-SPA-4006-03 | CLB-FC-2D-ST-SPA-4011-02 |
<tbody>
</tbody>