** Need help with Formula **

New Contributor

Hi All,


I need help writing a formula based on IF hypotheticals for instance, the value the below field will always change but will always start with one of the following as, me and ma



The same applies for these:



Essentially what I'm asking is, IF the start of the the switch begins as, me or ma and IF whether it is RJ45, 1000 BASE-LX etc. and IF it is Copper, Single-Mode etc I want it to automatically select the optic codes as shown below:




Any helps would be super appreciated.


Thanks in advance!



4 Replies


Let's say you have a table with all relevant combinations on a sheet named List in D2:G100.

And let's say that the data in your first three screenshots are in F2, P2 and Q2.

In the cell where you want the formula:


=INDEX(List!$G$2:$G$100, MATCH(1, (List!$D$2:$D$100=F2)*(List!$E$2:$E$100=P2)*(List!$F$2:$F$100=Q2), 0))


If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

Fill down if required.

@Hans Vogelaar 


Thanks for your help, i have tried to do the following as mentioned but i cant seem to get it right, please see screenshot below, as mentioned the switch(es) could be a combination of as, me or ma followed by a string of letters after as given I25 but will always start as as, me or ma. the idea is that based on the switch, interface type and access mode, i need it to populate the optic code.


Let me know what im doing wrong


Thanks once again




best response confirmed by Sergei Baklan (MVP)



=INDEX($G$18:$G$32, MATCH(1, ($D$18:$D$32=LEFT(I24,2))*($E$18:$E$32=J24)*($F$18:$F$32=K24), 0))

As mentioned before, if you do not have Microsoft 365 or Office 2021, but an older version, confirm the formula by pressing Ctrl+Shift+Enter.

Thanks Hans, that worked a treat although I had to remove the a-end access mode but that isn't significant in this case. I truly appreciate your help!