Using a between function in a vlookup

[davehuggins]

I have a rather long list of IP addresses in sheet1. In sheet two, columns A and B I have a "range" that the IP addresses in Sheet1 (Column A) will fall between.


I need to create a vlookup where the IP address in Sheet1 (example: 217.072.232.016) is looked up against column A & B in Sheet two, and returns a value from column C of Sheet two when the IP address value falls BETWEEN the values in cols A and B.


Example of sheet two:


217.072.232.000 217.072.232.064 206201

217.072.232.078 217.072.232.255 206202

080.125.175.128 080.125.175.191 208100


Does anyone know of a solution to this problem? Finding one would save my team hours of manaul work!


Thanks so much.

Dave

 

[Brant]

You could use an array formula. In Sheet1("B1"), enter:


"=INDEX(Sheet2!$C$1:$C$1000,MATCH(1,(Sheet1!A1>=Sheet2!$A$1:$A$1000)*(Sheet1!A1<=Sheet2!$B$1:$B$1000),0))"


(Change 1000 to your last row of data in Sheet2)


Enter it as an array formula (CTRL + SHIFT + ENTER), then drag the formula through the rest of column B.


That should work for you.

Brant

 

[tumbde]

This works great but i have few exception where this is breaking, some place when it is trying to compare an ip such as 192.5.1.26 and the Col B in Sheet 1 has 192.5.1.255 it is not able to calculate, Did you have a fix for that.

Thanks for the great tip.

 

[SirJB7]

@tumbde

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!