none
How to use vLookup with conditions when doing comparison between two sheets RRS feed

  • Question

  • I have two sheets that i want to do comparison and pull data from second sheet to the first sheet for a particular column.

    Let's call first sheet as Sheet A and second Sheet as Sheet B. 

    Sheet A has a column called Hostname that has some Server Hostnames and there are no duplicates.

    Sheet B has a column by the same name Hostname but there are duplicates in that sheet. Sheet B also has another column that has the Operating System Info related to each server and there are multiple rows related to each server, but not all rows have the Operating System Info

    So i would like to do a vLookup and use Hostname from sheet A as the lookup value and pull the corresponding Operating System Info from sheet B related to that hostname.

    1) So if i try to do a vLookup where Sheet B has multiple rows related to each hostname, kind of duplicate entries, is it possible to do a vLookup like this?

    2) If it is possible, then how do i pull only the values  by putting some kind of condition, like "Pull Hostname that has value in Sheet B in Column D  like Windows Server"  and it should match the exact string?

    Thanks


    Pallab Chakraborty

    Friday, January 4, 2019 10:39 PM

All replies

  • Excel 365 Pro Plus with Power Query (aka Get & Transform)
    Pull data conditionally.
    No formulas, no VBA macro.
    http://www.mediafire.com/file/pqy83it9cdkmc38/01_05_19a.xlsx/file
    http://www.mediafire.com/file/t7v7xi5bdc47w11/01_05_19a.pdf/file

    Saturday, January 5, 2019 7:32 PM
  • Hi Pallab Chakraborty,

    I can understand Hostname in Sheet A and duplicated Hostnames in Sheet B, but the Operating System info is not very clearly. What the Operating System info is listed in Sheet B?

    Could you provide a sample about your problem, you can share it to our email address:

    ibsofc@microsoft.com

    Note: Please add the URL of the case in the email subject or body, please make sure that you have hidden your private information.

    First please refer to the reply of Herbert Seidenberg, and check if the result is you need.

    If you still need to use formula to get the result, please provide a simple sample and I'm glad to help you.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact: tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, January 8, 2019 1:17 AM
    Moderator
  • I have sent an email to the email address with my issue and the Excel Sheet attached. Kindly have a look.

    Pallab Chakraborty

    Wednesday, January 9, 2019 12:28 PM
  • Hi,

    I received your email, but I don't know what kind of result you need.

    What is OS info? Could you provide a sample about OS info?

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact: tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.


    Thursday, January 10, 2019 7:26 AM
    Moderator
  • Hi.

    Vlookup will do a lookup verses a list with duplicates, but it will always return the first match it finds and ignore the rest. Sometimes that is sufficient. sometimes it is not.

    You can get around this by creating columns which concatenate the various values you are using for lookups and then use those. For example, on both sheets add a column which combines the values so you get "HostName Windows Server" and then use that for vlookup

    Good luck!

    Ethan


    Ethan Strauss

    Thursday, January 10, 2019 6:43 PM
  • OS Info is Operating System Info. So i want vLookup to only pull the rows from Sheet 1 that has anything which says Microsoft  Windows Server written in Column O and ignore all the other values that are in other rows corresponding to Column O. Hope you got now what i mean. But Ethan mentioned below that vLookup will always return the first match it finds and ignore the rest, so in that case what i am asking, will that be possible? You can let me know

    Pallab Chakraborty

    Friday, January 11, 2019 10:25 PM
  • If I understand what you want correctly, that's difficult to do in Excel without VBA.

    You can use various of Excel's lookup functions, but they need to lookup in different ranges so that the second lookup starts looking the row after the first lookup's result. I don't have time to write out how to do that right now.

    I also found a different way to do it (that I have never tried). Take a look at https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/

    If that doesn't work, I can write out my solution when I have time.

    Ethan


    Ethan Strauss

    Monday, January 14, 2019 4:37 PM