Forum Discussion
Solved
Conditional Vlookup
Hello All, I am trying to perform a conditional vlookup based on values. For example, I want to fetch server name from another sheet and only want to use vlookup where the columns shows only "nul...
So a formula couldn't do that in the same cell where the NULL appears. Instead, you need to add what we often call a "helper column".
Then, in that helper column you'd write your VLOOKUP formula, and, assuming that column B contains the reference to use in VLOOKUP, and that column C contains the "null" value, the resulting formula would go something like
=IF(C11="null", VLOOKUP(B11,othertable,offset,0),C11)
This would retain whatever value is in column C where that value is not "null" but, if it is "null", replace it with whatever is in that remote table where you're performing the lookup.
If that doesn't help you, might I request that you post a copy of the workbook (including both sheets) on OneDrive or GoogleDrive, with a link pasted here that grants access.
My Apologies , if I was unable to answer. Here ,I am trying to perform vlookup for the column Server and I only like to apply vlookup where it only shows "NULL" values. In the last row it shows null and corresponding to it the value is "md02p" and using this value , I want to search values from another sheet and like to replace it where is shows NULL. In the given table which happens to be the last row from the previously mentioned table.
Date Error Server
5/2/2023 md02p null
Please see my inputs against the questions being asked.
Are the "null values" in the sheet where the VLOOKUP resides?
>>No, I just need to perform vlookup where values showing as NULL, since other rows has values , so I do not required to perform values.
Or in the other sheet, the one that VLOOKUP is looking at?
>> VLOOKUP will find values from other sheet where the column shows NULL values given in the table.
Is the table you show in your inquiry the one from which the lookup is to retrieve a value,
>> VLOOKUP needs to fetch values from other sheet which has database name as md02p and corresponding to it , VLOOKUP will try to fetch Server name from other sheet and will replace the values NULL to server name
or is it the one containing the lookup formula, and only using it as in the bottom row where "null" appears?
>> In this case , it happens to be bottom rows , but I have many values which shown NULL and all the NULL should replaced by server name. ( DB name and Server name) sits in different sheet.
Regards
Date Error Server
5/2/2023 md02p null
Please see my inputs against the questions being asked.
Are the "null values" in the sheet where the VLOOKUP resides?
>>No, I just need to perform vlookup where values showing as NULL, since other rows has values , so I do not required to perform values.
Or in the other sheet, the one that VLOOKUP is looking at?
>> VLOOKUP will find values from other sheet where the column shows NULL values given in the table.
Is the table you show in your inquiry the one from which the lookup is to retrieve a value,
>> VLOOKUP needs to fetch values from other sheet which has database name as md02p and corresponding to it , VLOOKUP will try to fetch Server name from other sheet and will replace the values NULL to server name
or is it the one containing the lookup formula, and only using it as in the bottom row where "null" appears?
>> In this case , it happens to be bottom rows , but I have many values which shown NULL and all the NULL should replaced by server name. ( DB name and Server name) sits in different sheet.
Regards
Thanks a ton for your valuable inputs and I was able to achieve desired results and now I was able to fetch values from other sheet ,but now I have a task to do where I need to replace all NULLS with it's corresponding values. Like in given below sheet , rows with "null's" needs to be replaced with "phazd0255lx01.phibred.com" values.
| Server | New values |
| phazd0255lx01.phibred.com | phazd0255lx01.phibred.com |
| phazd0255lx01.phibred.com | phazd0255lx01.phibred.com |
| null | phazd0255lx01.phibred.com |
| phazd0255lx01.phibred.com | phazd0255lx01.phibred.com |
| phazd0255lx01.phibred.com | phazd0255lx01.phibred.com |
Regards
- Sorry for delayed response .. Yes, I was able to achieve the desired results.
The "New Values" column has all the correct values, right?
So you could just leave that as the column to use.
The alternative, if this is a once-and-done situation, is to copy from there to the original "Server" column, but make sure you use "Paste Special...Values" rather than plain "Paste"