Forum Discussion
** HELP ** MATCH/INDEX
hi HansVogelaar ,
I believe you helped me with this Index/Match formula some time ago but i need some assistance i've had to change the way it functions and its not working properly now.
so the way it is suppose to work is that when it recognises its suppose to pull through one of the following messages, dependment on the first 2 letters in I22 and j22 so in this instance if i22 = ma2.lcblk and j22 = 1000 base-sx (MMF) it should return "##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ORDER RUGGEDISED FIBRES FOR THIS JOB #####" however it does not and it only returns the error "*" (Image of current formula below"
| RJ45 | Copper | ##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ALLOW FOR LONGER CAT5 CABLING FOR THIS JOB ##### |
| 1000 Base-T (RJ45) | Copper | ##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ALLOW FOR LONGER CAT5 CABLING FOR THIS JOB ##### |
| 1000 BASE-LX | Single-Mode | ##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ORDER RUGGEDISED FIBRES FOR THIS JOB ##### |
| 1000 BASE-SX | Multi-Mode | ##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ORDER RUGGEDISED FIBRES FOR THIS JOB ##### |
| 10GBASE-LR | Single-Mode | ##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ORDER RUGGEDISED FIBRES FOR THIS JOB ##### |
as in the below image it should have returned " ##### PLEASE NOTE THE EAD MULTI-CHASSIS IS LOCATED IN A DIFFERENT RACK TO THE ALLOCATED SWITCH. THEREFORE FE WILL NEED TO ORDER RUGGEDISED FIBRES FOR THIS JOB #####" instead its returning "*"
In this second example where it says as101.cmeas it works as intended however if it begins with me or ma it seems to not work.
any help would be greatly appreciated.
1 Reply
sruk89 The INDEX array and the MATCH lookup_array need to be the same height. MATCH will return a row number that is passed to INDEX... if the row number exceeds the number of rows in the INDEX array, it will return an error.
Your formula is currently referencing K28:K32 as the INDEX array (5 rows), and D18:D32 / E18:E32 as the lookup_array in MATCH (15 rows). It would be more appropriate to have the list of applicable messages in the range H18:H32, for example, and use that range as the INDEX array.
However, looking at your screenshots, there doesn't appear to be any difference in the Interface Type between the 3 Switches, so you could simply reference the summary table in range I28:K32. For example:
=INDEX(K28:K32, MATCH(J22, I28:I32, 0))...so long as the values in range I28:I32 also contain the appropriate (SMF) or (MMF) suffixes (in the screenshot they currently don't). I hope that makes sense.
