Formula to compare last octet of ipadress against a defined IP address and return text.

[@jerry]

Hi,

I want to compare last octet of IP address against a defined value and return a text value in the adjacent cell.

Last Octet Location
0.1 Europe
0.8 America
0.9 Asia
0.1 Australia
.6, .7, .11, .12 Africa

Eg: IPaddress is 10.10.10.8 then it should compare the last octet which is .8 and return the value America

Thanks !!

[XOR LX]

Hi,

Can you clarify whether 0.1 is Europe or Australia? They both have this value in your list.

Regards

[@jerry]

.10 is australia

[XOR LX]

The simplest way would be to set up a small table, e.g. with E1:E8 containing 1, 6, 7, 8, 9, 10, 11, 12 and F1:F8 containing Europe, Africa, Africa, America, Asia, Australia, Africa, Africa.

Then, assuming A1 contains your IP Address:

=VLOOKUP(--SUBSTITUTE(RIGHT(A1, 2), ".", ), $E$1:$F$8, 2, 0)

Regards

[@jerry]

Great !! It works perfectly fine.

[XOR LX]

You're welcome!