Row 1 has headings:
- CIDR
- IP
- First IP Int
- Last IP Int
- Test IP Int
- IP In Range
Column A contains CIDRs (e.g. 3.2.1.0/30)
Column B contains IPs (e.g. 3.2.1.2)
Column C contains a formula to calculate the first IP in the CIDR: =INDEX(TEXTSPLIT($A2, "."), 1) * 256^3 + INDEX(TEXTSPLIT($A2, "."), 2) * 256^2 + INDEX(TEXTSPLIT($A2, "."), 3) * 256 + INDEX(TEXTSPLIT($A2, {".","/"}), 4)
Column D contains a formula to calculate the last IP in the CIDR: =C2 + POWER(2,32-INDEX(TEXTSPLIT($A2, {".","/"}), 5)) - 1
Column E contains a forumla to calculate the IP: =INDEX(TEXTSPLIT(B2, "."), 1) * 256^3 + INDEX(TEXTSPLIT(B2, "."), 2) * 256^2 + INDEX(TEXTSPLIT(B2, "."), 3) * 256 + INDEX(TEXTSPLIT(B2, "."), 4)
Column F checks whether the IP in column B is in the CIDR from column A: =AND($E2>=$C2,$E2<=$D2)