Q:

How to specify a range of values in Excel?

I have an excel report that has two columns 'Site Name' and 'Asset IP Address' in it. I have uploaded a sample report here. I need a report that shows me the range IP by site, like this:

Assessment Site 1 192.0.2.134 – 192.0.2.201  
Assessment Site 2 192.0.2.203 – 192.0.2.250 and so on

I have tried using VLOOKUP and CountIF but I am unable to make this work. Any suggestions are most appreciated. Thanks in  advance.

A:

If you want a purely formula-based solution then, assuming you put e.g. Assessment Site 1 in E2, this array formula** in F2:
=INDEX($B$2:$B$33,MATCH(MIN(IF($A$2:$A$33=E2,0+SUBSTITUTE($B$2:$B$33,".",""))),IF($A$2:$A$33=E2,0+SUBSTITUTE($B$2:$B$33,".","")),0))&" - "&INDEX($B$2:$B$33,MATCH(MAX(IF($A$2:$A$33=E2,0+SUBSTITUTE($B$2:$B$33,".",""))),IF($A$2:$A$33=E2,0+SUBSTITUTE($B$2:$B$33,".","")),0))
Copying down will give similar results for Sites listed in E3, E4, etc.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

