SOLVED

# ** Need help with Formula **

Copper Contributor

# ** 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.

4 Replies

# Re: ** Need help with Formula **

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.

# Re: ** Need help with Formula **

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 VI_Migration (Silver Contributor)
Solution

# Re: ** Need help with Formula **

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.

# Re: ** Need help with Formula **

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!
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: ** Need help with Formula **

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.