Iferror with Vlookup returning wrong data
- Thread starter bobhc
- Start date
bobhc
Excel Ninja
Me again
Realized that I should be using absolute reference, when I do it shows data.....but when Albertson, Kathy is put in the cells all return 0,....but I thought all data cells would show "No Data" is this due to the fact that all cells are empty, and if so how do you get iferror do return to "No Data" that is in the formula??
Realized that I should be using absolute reference, when I do it shows data.....but when Albertson, Kathy is put in the cells all return 0,....but I thought all data cells would show "No Data" is this due to the fact that all cells are empty, and if so how do you get iferror do return to "No Data" that is in the formula??
NARAYANK991
Excel Ninja
Hi Bob ,
A few things are wrong here :
Against REGION , your formula has J1 as the first parameter for the VLOOKUP function ; this should be J2.
If you want Region and Account to display "No Data" , you can use :
=IF(ISBLANK(VLOOKUP($J$2,Data_Range,2,FALSE)),"No Data")
IFERROR needs to be used only when you know that the formula can return an error ; in this case , if you are going to have a drop-down in J2 , and you are sure that no name which is not present in column A can be entered in J2 , then you don't need an IFERROR , since you are sure that the VLOOKUP function will always find a match.
Lastly , since you have multiple entries for each person , there is no way you will be able to retrieve those multiple entries using only a VLOOKUP ; you need to use a combination involving INDEX , SMALL , IF , ROW.
Narayan
A few things are wrong here :
Against REGION , your formula has J1 as the first parameter for the VLOOKUP function ; this should be J2.
If you want Region and Account to display "No Data" , you can use :
=IF(ISBLANK(VLOOKUP($J$2,Data_Range,2,FALSE)),"No Data")
IFERROR needs to be used only when you know that the formula can return an error ; in this case , if you are going to have a drop-down in J2 , and you are sure that no name which is not present in column A can be entered in J2 , then you don't need an IFERROR , since you are sure that the VLOOKUP function will always find a match.
Lastly , since you have multiple entries for each person , there is no way you will be able to retrieve those multiple entries using only a VLOOKUP ; you need to use a combination involving INDEX , SMALL , IF , ROW.
Narayan
bobhc
Excel Ninja
Good day NARAYANK991
Took you advice re Vlookup.
Have tried to put together a new multi lookup but I have lost my way with the code would be most grateful if you could have a look. Both examples have Dropdown list for persons.
https://dl.dropbox.com/u/75495784/IfError%20with%20Vlookup.xlsx
Took you advice re Vlookup.
Have tried to put together a new multi lookup but I have lost my way with the code would be most grateful if you could have a look. Both examples have Dropdown list for persons.
https://dl.dropbox.com/u/75495784/IfError%20with%20Vlookup.xlsx
NARAYANK991
Excel Ninja
Hi Bob ,
Can you download the file from here and check it out ?
https://docs.google.com/open?id=0B0KMpuzr3MTVYUFoSkFsV1hKaTA
Narayan
Can you download the file from here and check it out ?
https://docs.google.com/open?id=0B0KMpuzr3MTVYUFoSkFsV1hKaTA
Narayan
NARAYANK991
Excel Ninja
Hi Bob ,
So now , can I wish you a great weekend ?!
Narayan
So now , can I wish you a great weekend ?!
Narayan
NARAYANK991
Excel Ninja
Hi Bob ,
Do you mean in the multiple lookups formulae ?
If so , then you can use MATCH , but only to retrieve the first occurrence , since MATCH will find the first occurrence which fulfills the criteria i.e. if the third parameter in the MATCH function is 0 , then it finds an exact match , if it is 1 , it finds the largest value less than or equal to the lookup value , and so on.
If you have to retrieve multiple occurrences , using SMALL with a second parameter going from 1 , 2 , 3 ,... makes it easy to retrieve the first , second , third ,... matching values.
When you use SMALL , you don't need MATCH ; using the IF within the SMALL function , and entering the entire formula as an array formula , means you are building up an array of TRUE / FALSE values ; TRUE where the values in the range match the lookup value , and FALSE elsewhere. The IF statement is replacing the TRUE values with the corresponding row numbers , leaving the FALSE values unchanged.
Thus , if the range is say 6 cells , you may end up with an array {FALSE;FALSE;7;FALSE;9;10}.
Now , when you take the smallest value from this array , you get 7 ; when you want the next smallest , you will get 9 , and for the third smallest value , you will get 10. Using these values in conjunction with the INDEX function allows you to get the relevant values.
In this case , since we are looking at the first , second and third occurrences only by their row number , retrieving the different data such as Region , Account Number , Order Amount and Month is just a matter of changing the first parameter of the INDEX function , so that we look at columns B , C , D and E.
Narayan
Do you mean in the multiple lookups formulae ?
If so , then you can use MATCH , but only to retrieve the first occurrence , since MATCH will find the first occurrence which fulfills the criteria i.e. if the third parameter in the MATCH function is 0 , then it finds an exact match , if it is 1 , it finds the largest value less than or equal to the lookup value , and so on.
If you have to retrieve multiple occurrences , using SMALL with a second parameter going from 1 , 2 , 3 ,... makes it easy to retrieve the first , second , third ,... matching values.
When you use SMALL , you don't need MATCH ; using the IF within the SMALL function , and entering the entire formula as an array formula , means you are building up an array of TRUE / FALSE values ; TRUE where the values in the range match the lookup value , and FALSE elsewhere. The IF statement is replacing the TRUE values with the corresponding row numbers , leaving the FALSE values unchanged.
Thus , if the range is say 6 cells , you may end up with an array {FALSE;FALSE;7;FALSE;9;10}.
Now , when you take the smallest value from this array , you get 7 ; when you want the next smallest , you will get 9 , and for the third smallest value , you will get 10. Using these values in conjunction with the INDEX function allows you to get the relevant values.
In this case , since we are looking at the first , second and third occurrences only by their row number , retrieving the different data such as Region , Account Number , Order Amount and Month is just a matter of changing the first parameter of the INDEX function , so that we look at columns B , C , D and E.
Narayan