Forum Discussion
Solved
** Need help with Formula **
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
Switch:
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!
Try
=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.
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.
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 🙂
Try
=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.
