Home
Join

15 Replies

  • Welcome.

    What have you tried? Where are you stuck?

    Was this post helpful? thumb_up thumb_down
  • I'm not sure if I got it correctly. 

    Do you want to use Sheet 1 as a search/results sheet only, and all data is store in other sheets, am I right?

    Are the IPs and host names unique or can be duplicated across the workbook?

    Are all IPs and host stored only in two first columns of each sheets?

    whenever an IP address is copied in Sheet 1 first column, once I hit enter the corresponding hostnames should be searched within the whole workbook and copied in front of respective IP address in second column .

    If you tape ip/paste IP in the first column in Sheet 1, do you want to add extra column before the first column or to edit the IP and put everything into one cell in the second column?

    Can you show example, what do you expect to get?

    Was this post helpful? thumb_up thumb_down
  • Might have been easier in Access

    Spice (1) flagReport
    1 found this helpful thumb_up thumb_down
  • Briser_fae_the_broch wrote:

    Might have been easier in Access

    Came here to say this, Access is what you want.

    There are also pre-built scripts for Access you can grab online that will help inventory and categorise these.

    Was this post helpful? thumb_up thumb_down
  • well - the obvious formula is xlookup

    and concatenate a lookup for each sheet.

    That does assume you have IP address and Host Name in columns or rows

    Spice (2) flagReport
    Was this post helpful? thumb_up thumb_down
  • Please see above photo,this is what I need to do in excel.

    Thanks

    Was this post helpful? thumb_up thumb_down
  • Andrew_F wrote:

    well - the obvious formula is xlookup

    and concatenate a lookup for each sheet.

    That does assume you have IP address and Host Name in columns or rows

    And wrap it in an IF statement, so that an empty search cell doesn't return an error.  Not strictly necessary.  But, error notices are annoying to look at.

    Was this post helpful? thumb_up thumb_down
  • You already have a chart of IP cross referencd to name? Sounds llke a VLOOKUP.

    I must be missing something because Ctrl-f would already find either.

    Was this post helpful? thumb_up thumb_down
  • VLOOKUP across multiple sheets video

    https://youtu.be/2erErC7LvPY

    Everybody who works with Excel should add his channel to their subscriptions.  LG is one of the top 5 rated channels on Excel tips.

    Spice (1) flagReport
    Was this post helpful? thumb_up thumb_down
  • jessevas wrote:

    Andrew_F wrote:

    well - the obvious formula is xlookup

    and concatenate a lookup for each sheet.

    That does assume you have IP address and Host Name in columns or rows

    And wrap it in an IF statement, so that an empty search cell doesn't return an error.  Not strictly necessary.  But, error notices are annoying to look at.

    you need to have a look at Xlookup - it differs from VLookup in that it can return a value when there is no result - no if statement needed.

    Spice (2) flagReport
    Was this post helpful? thumb_up thumb_down
  • The simplest formula to past into B2 cell, then fill the column. 

    =IF(ISBLANK($A2),"",XLOOKUP($A2,'city 1'!$B$1:$B$999,'city 1'!$A$1:$A$999,XLOOKUP($A2,'city 2'!$B$1:$B$999,'city 2'!$A$1:$A$999,XLOOKUP($A2,'city 3'!$B$1:$B$999,'city 3'!$A$1:$A$999,"not Found",0),0),0))

    The formula could be also done in different way, however for what you need, I think it's the simplest one.

    You can also create a macro for that I you need to hide the formula.

    Hope that helps.

    Was this post helpful? thumb_up thumb_down
  • Andrew_F wrote:

    jessevas wrote:

    Andrew_F wrote:

    well - the obvious formula is xlookup

    and concatenate a lookup for each sheet.

    That does assume you have IP address and Host Name in columns or rows

    And wrap it in an IF statement, so that an empty search cell doesn't return an error.  Not strictly necessary.  But, error notices are annoying to look at.

    you need to have a look at Xlookup - it differs from VLookup in that it can return a value when there is no result - no if statement needed.

    Thanks for that.  I'm too ingrained into using nested IFs and VLOOKUPs because I also use LibreOfficeCalc.  

    There are actually 2 more new Excel lookup functions.  I haven't been able to try them yet, and can't quite remember what they are.  I just recall seeing them in LG's videos and being very impressed with them.

    Was this post helpful? thumb_up thumb_down
  • This worked and good to start with...

    What if I need to display other columns too (C, D E) along B in Sheet 1 after entering IP address, will it show too?

    Thanks

    Was this post helpful? thumb_up thumb_down
  • look up the syntax of the function you've used and see if you can work out what it does.

    There will be a way you can use multiple formulas to get the results into one field - using concatenate - or you may be better to put results in adjacent cells.

    Spice (1) flagReport
    Was this post helpful? thumb_up thumb_down
  • Yes it can, but it would be much easier if you would swap IP and host name column.

    Then you just need to modify formula and expand result range with more columns as below:

    =IF(ISBLANK($A2),"",XLOOKUP($A2,'city 1'!$A$1:$A$999,'city 1'!$B$1:$E$999,XLOOKUP($A2,'city 2'!$A$1:$A$999,'city 2'! $B$1:$E$999,XLOOKUP($A2,'city 3'!$A$1:$A$999,'city 3'! $B$1:$E$999,"not Found",0),0),0))

    be aware that I've already change look up range to column A

    Was this post helpful? thumb_up thumb_down

Read these next...