Forum Discussion

AlokBeheria's avatar
AlokBeheria
Brass Contributor
May 17, 2023

Vlookup unable to fetch desired values

Hello All,

 

I have 2 sheets in which I am trying to find vlookup to search values from 1 sheet and find the corresponding match from other sheet. For few rows , I am getting the results , but for few columns , I am getting #NA errors.  In the following workbook, I have 2 sheets in which I need to find values for server and corresponding to it , DB name is displayed. what I want irrespective of row number if it find the match then it should able to display DB name. Not sure , if this can be done using vlookup. Also, tried to use INDEX and Match function , but unable to use it as I am st

 

Sheet 1 Sheet 2
row numberServer DB Namerow numberServer DB Name
1sw252.phibred.comAGILEPD10sw252.phibred.comAGILEPD
2sw46.phibred.comAGILEQA14sw46.phibred.comAGILEQA
3lxjh823.phibred.combiodev15lxjh823.phibred.combiodev
4sw251.phibred.comBIOMART17sw251.phibred.comBIOMART
5lxjh833.phibred.combioneer35lxjh833.phibred.combioneer
6lxjh826.phibred.combioneerq40lxjh826.phibred.combioneerq
7sw251.phibred.comBIOPRD45sw251.phibred.comBIOPRD
8lxjh843.phibred.combioprd67lxjh843.phibred.combioprd

 

Reggards

excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 17, 2023

    AlokBeheria 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), with the formula you tried, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • AlokBeheria's avatar
      AlokBeheria
      Brass Contributor
      May 18, 2023

      Hello Hans,

       

      I uploaded the sample data where in I am trying to get values(DB_NAME) from "sheet 2" and you may discover that few rows , it's pulling up the values while other rows , it does shows #NA and which is where I am just trying to find a way to populate values from sheet 2.

       

      Regards

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 18, 2023

        AlokBeheria 

        The lookup is fairly useless, since the Host_name column on Sheet2 contains large numbers of duplicate values while the corresponding DB_Name values are different. VLOOKUP will simply return the first match that it encounters.

        Apart from that, your lookup range in the formula in B2 is Sheet2!A1:B331. This is a relative reference, so it changes when you fill or copy the formula down. You should use Sheet2!$A$1:$B$331 (or use a named range or a table). You'll still have a few #N/A:

        In B9 because there is no Host_name sw337 on Sheet2.

        In B10 because there is no Host_name eluxd06 on Sheet2.

Resources