Results 1 to 14 of 14

Thread: Calling Excel Gurus.....

  1. #1
    Basically lazy robd's Avatar
    Join Date
    May 2005
    Location
    Nr Cambridge
    Posts
    3,696
    Rep Power
    12

    Calling Excel Gurus.....

    OK, I have solved this already but in a v clunky way and am sure there must be a simpler mechanism.

    I have an inventory of PCs and wish to associate them with their home location by the IP address (it's the only geographic identifier I have - each location uses a unique IP range).

    I have a separate sheet with column A looking something like this

    10.2.30
    10.2.31

    and column B looking like

    Site A
    Site B

    and so on

    This is my lookup chart mapping IP address stems to sites.

    My problem is that the inventory I gather has the full IP addresses (and I do want the full address for other reasons) e.g 10.2.30.1,10.2.30.105, etc

    So how, in as few steps as possible can I discern (and display in an adjacent cell to the IP address) the site of a PC given the data that I have to hand?

    Robert

  2. #2
    Registered User
    Join Date
    Jan 2009
    Location
    Stevenage
    Posts
    540
    Rep Power
    8

    Re: Calling Excel Gurus.....

    Assuming you have full IP addresses in Column A, and you are doing a lookup against your table in Sheet2 I would do it this way:

    =VLOOKUP(LEFT(SUBSTITUTE(A1,".","^",3),FIND("^",SU BSTITUTE(A1,".","^",3))-1),Sheet2!A:B,2)

    Effectively you are substituting the third full stop with a ^ and then trimming all the text away after that to give you just the first 3 parts of the IP address. You then use that text to lookup against your second sheet.

  3. #3
    Registered User Trousers's Avatar
    Join Date
    Sep 2004
    Posts
    1,349
    Rep Power
    10

    Re: Calling Excel Gurus.....

    dare i ask how many sites you expect to have?

    and in the IP addy do you always drop the leading/packing zeros

  4. #4
    Basically lazy robd's Avatar
    Join Date
    May 2005
    Location
    Nr Cambridge
    Posts
    3,696
    Rep Power
    12

    Re: Calling Excel Gurus.....

    Thanks.

    The VLOOKUP solution works but I remembered now why I ended up using the clunky mix of MATCH and INDEX previously - it's because my list of IP addresses for sites is not necessarily always right up to date (which I know is a weakness in the design but it's the way things are with our network people ) so I need for any IP address stems that do not have an EXACT match in the lookup table to report an error (so I know to update the lookup table) rather than displaying the nearest corresponding value which is what VLOOKUP does.

    Trousers, the addresses do not display any leading or packing zeroes.

    Rob

  5. #5
    Registered User
    Join Date
    Jan 2009
    Location
    Stevenage
    Posts
    540
    Rep Power
    8

    Re: Calling Excel Gurus.....

    Quote Originally Posted by robd View Post
    <snip>

    so I need for any IP address stems that do not have an EXACT match in the lookup table to report an error (so I know to update the lookup table) rather than displaying the nearest corresponding value which is what VLOOKUP does.

    Rob
    Sorry, I meant to put an extra ",0" on the end. That causes VLOOKUP to return an #N/A error when an exact match is not found. You could use a combination of =IF(ISNA([formula])=true,[do something],[formula]) to catch those errors.

    =VLOOKUP(LEFT(SUBSTITUTE(A1,".","^",3),FIND("^",SU BSTITUTE(A1,".","^",3))-1),Sheet2!A:B,2,0)

  6. #6
    Basically lazy robd's Avatar
    Join Date
    May 2005
    Location
    Nr Cambridge
    Posts
    3,696
    Rep Power
    12

    Re: Calling Excel Gurus.....

    OK, solved it now and thanks to Froli for the heads up on a simpler way of getting the IP stem. The full formula - not for the faint hearted - to do this in a single cell is

    =INDEX('Library IP Addresses'!$A$1:$B$200,MATCH(LEFT(SUBSTITUTE(T2,". ","^",3),FIND("^",SUBSTITUTE(T2,".","^",3))-1),'Library IP Addresses'!$A$1:$A$200,0),2)

    where T2 is the cell containing the full IP address.

    This then gives a #N/A if an exact match is not found.

    STOP: As I posted so did frolicols with what looks like a simpler solution - I'll try that too.

    Rob

  7. #7
    Taxi Dancer Aurion's Avatar
    Join Date
    Jan 2007
    Posts
    97
    Rep Power
    9

    Re: Calling Excel Gurus.....

    Quote Originally Posted by frolicols View Post
    Assuming you have full IP addresses in Column A, and you are doing a lookup against your table in Sheet2 I would do it this way:

    =VLOOKUP(LEFT(SUBSTITUTE(A1,".","^",3),FIND("^",SU BSTITUTE(A1,".","^",3))-1),Sheet2!A:B,2)

    Effectively you are substituting the third full stop with a ^ and then trimming all the text away after that to give you just the first 3 parts of the IP address. You then use that text to lookup against your second sheet.
    Quote Originally Posted by robd View Post
    Thanks.

    The VLOOKUP solution works but I remembered now why I ended up using the clunky mix of MATCH and INDEX previously - it's because my list of IP addresses for sites is not necessarily always right up to date (which I know is a weakness in the design but it's the way things are with our network people ) so I need for any IP address stems that do not have an EXACT match in the lookup table to report an error (so I know to update the lookup table) rather than displaying the nearest corresponding value which is what VLOOKUP does.

    Trousers, the addresses do not display any leading or packing zeroes.

    Rob
    Rob

    You can still use the vlookup but set the "range_lookup" to false to stop it finding the nearest value, it will then just return an error.

    =VLOOKUP(LEFT(SUBSTITUTE(A1,".","^",3),FIND("^",SU BSTITUTE(A1,".","^",3))-1),Range,2,FALSE)




    Beaten to it...
    Last edited by Aurion; 3rd-February-2009 at 05:46 PM. Reason: too slow.........

  8. #8
    Registered User
    Join Date
    Oct 2007
    Location
    Crewe, Cheshire
    Posts
    1,681
    Rep Power
    9

    Re: Calling Excel Gurus.....

    Vlookup...

    (and yes I know it has been said, lol)

  9. #9
    The Dashing Moderator
    Join Date
    Mar 2003
    Location
    Midlands
    Posts
    3,556
    Rep Power
    12

    Re: Calling Excel Gurus.....

    Actually I thought that the clever bit was this:
    Quote Originally Posted by frolicols View Post
    SUBSTITUTE(A1,".","^",3)
    I'd have put 3 separate, nested FIND commands in, one for each dot.
    Love dance, will travel

  10. #10
    Registered User
    Join Date
    Jan 2009
    Location
    Stevenage
    Posts
    540
    Rep Power
    8

    Re: Calling Excel Gurus.....

    Quote Originally Posted by robd View Post
    OK, solved it now and thanks to Froli for the heads up on a simpler way of getting the IP stem. The full formula - not for the faint hearted - to do this in a single cell is

    =INDEX('Library IP Addresses'!$A$1:$B$200,MATCH(LEFT(SUBSTITUTE(T2,". ","^",3),FIND("^",SUBSTITUTE(T2,".","^",3))-1),'Library IP Addresses'!$A$1:$A$200,0),2)

    where T2 is the cell containing the full IP address.

    This then gives a #N/A if an exact match is not found.

    STOP: As I posted so did frolicols with what looks like a simpler solution - I'll try that too.

    Rob
    Sometimes it is better to use an INDEX/MATCH instead of a VLOOKUP as the latter will only return cells to the right of the lookup value. The former works both ways.

  11. #11
    The Dashing Moderator
    Join Date
    Mar 2003
    Location
    Midlands
    Posts
    3,556
    Rep Power
    12

    Re: Calling Excel Gurus.....

    Quote Originally Posted by frolicols View Post
    Sometimes it is better to use an INDEX/MATCH instead of a VLOOKUP as the latter will only return cells to the right of the lookup value. The former works both ways.
    Also, if you want more than one result column from another sheet it's can be more efficient to have a single Match column and several Index columns, as it only needs to find the correct row once.
    Love dance, will travel

  12. #12
    Registered User Beowulf's Avatar
    Join Date
    Jul 2003
    Location
    The Beoverse
    Posts
    7,985
    Rep Power
    13

    Re: Calling Excel Gurus.....

    Quote Originally Posted by frolicols View Post
    Sometimes it is better to use an INDEX/MATCH instead of a VLOOKUP as the latter will only return cells to the right of the lookup value. The former works both ways.
    Quote Originally Posted by DavidY View Post
    Also, if you want more than one result column from another sheet it's can be more efficient to have a single Match column and several Index columns, as it only needs to find the correct row once.
    x 2

    one caveat with using VLOOKUP is the optional 4th parameter.. the Range_lookup. if it's omitted it's assumed to be true. what that means is if there's not an exact match it'll find the closest one. Now this is ok in some circumstances but then only if the table_array is sorted. if it's randomly unsorted Setting range_lookup to TRUE or omitting it can cause .. erm.. "interesting" results.

    I always set it to False to force it to find an exact match. then wrap the statement in an IF(ISNA(Vlookup function),Vlookup Result, Default fail value)

  13. #13
    Basically lazy robd's Avatar
    Join Date
    May 2005
    Location
    Nr Cambridge
    Posts
    3,696
    Rep Power
    12

    Re: Calling Excel Gurus.....

    Quote Originally Posted by Beowulf View Post
    one caveat with using VLOOKUP is the optional 4th parameter.. the Range_lookup. if it's omitted it's assumed to be true.
    Range lookup isn't exactly the best description is it - something like 'exact match' might be more meaningful for that parameter. I also think the default value would be more useful if reversed i.e must match exactly if the parameter is omitted but I guess different people will have different preferences and MS had to go one way or the other with it.

  14. #14
    Registered User Beowulf's Avatar
    Join Date
    Jul 2003
    Location
    The Beoverse
    Posts
    7,985
    Rep Power
    13

    Re: Calling Excel Gurus.....

    Quote Originally Posted by robd View Post
    Range lookup isn't exactly the best description is it - something like 'exact match' might be more meaningful for that parameter. I also think the default value would be more useful if reversed i.e must match exactly if the parameter is omitted but I guess different people will have different preferences and MS had to go one way or the other with it.
    I always thought the description was wrong and the default Ar$e about face too.. It caught me out once or twice when I first started using it until I figured out the problem. Just something else to keep in the back of your mind

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel and null values - guru advice sought
    By Gadget in forum Geeks' Corner
    Replies: 7
    Last Post: 19th-December-2007, 07:42 PM
  2. Excel Formula.
    By Lee Bartholomew in forum Chit Chat
    Replies: 11
    Last Post: 14th-August-2007, 12:50 PM
  3. Excel sort & delete question....
    By Will in forum Geeks' Corner
    Replies: 22
    Last Post: 26th-May-2007, 11:14 PM
  4. excel
    By under par in forum Geeks' Corner
    Replies: 12
    Last Post: 8th-March-2007, 10:51 PM

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •