| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup dependent on a validation table
I have a cell that has a validation table that allows data to be entered that
is not available in my "list". It brings up the information pop up window and you click ok to keep going with the information entered that is not on the list. Another cell has a vlookup table dependent on the data in the validation cell. I would like for the vlookup cell to return nothing if the validation cell had data entered on it that is not included on the validation list. If I put the last argument in the Vlookup, it returns an N/A #. My formula looks like this: =IF($E$120, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "") Any thoughts? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup dependent on a validation table
instead of your vlookup put this in the cells
in Excel 2003 =ISERROR('your VLOOKUP without quotes shown here',"",'your VLOOKUP without quotes shown here') in 2007 you can shorten that too =IFERROR('your VLOOKUP without quotes shown here',"") "saltnsnails" wrote: I have a cell that has a validation table that allows data to be entered that is not available in my "list". It brings up the information pop up window and you click ok to keep going with the information entered that is not on the list. Another cell has a vlookup table dependent on the data in the validation cell. I would like for the vlookup cell to return nothing if the validation cell had data entered on it that is not included on the validation list. If I put the last argument in the Vlookup, it returns an N/A #. My formula looks like this: =IF($E$120, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "") Any thoughts? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup dependent on a validation table
It says I have too few arguments. :(
"Sheeloo" wrote: instead of your vlookup put this in the cells in Excel 2003 =ISERROR('your VLOOKUP without quotes shown here',"",'your VLOOKUP without quotes shown here') in 2007 you can shorten that too =IFERROR('your VLOOKUP without quotes shown here',"") "saltnsnails" wrote: I have a cell that has a validation table that allows data to be entered that is not available in my "list". It brings up the information pop up window and you click ok to keep going with the information entered that is not on the list. Another cell has a vlookup table dependent on the data in the validation cell. I would like for the vlookup cell to return nothing if the validation cell had data entered on it that is not included on the validation list. If I put the last argument in the Vlookup, it returns an N/A #. My formula looks like this: =IF($E$120, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "") Any thoughts? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup dependent on a validation table
Sorry, I forgot the IF statement...
Try... =IF(ISERROR(VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE)),"",VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE )) bascially it checks ISERROR(VLOOKUP), if true returns "", otherwise retruns VLOOKUP I have removed the $ around E12 so that you can copy down... "saltnsnails" wrote: It says I have too few arguments. :( "Sheeloo" wrote: instead of your vlookup put this in the cells in Excel 2003 =ISERROR(VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ),"",VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 )) in 2007 you can shorten that too =IFERROR('your VLOOKUP without quotes shown here',"") "saltnsnails" wrote: I have a cell that has a validation table that allows data to be entered that is not available in my "list". It brings up the information pop up window and you click ok to keep going with the information entered that is not on the list. Another cell has a vlookup table dependent on the data in the validation cell. I would like for the vlookup cell to return nothing if the validation cell had data entered on it that is not included on the validation list. If I put the last argument in the Vlookup, it returns an N/A #. My formula looks like this: =IF($E$120, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "") Any thoughts? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup dependent on a validation table
AWESOME!!!! Thank you!
"Sheeloo" wrote: Sorry, I forgot the IF statement... Try... =IF(ISERROR(VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE)),"",VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE )) bascially it checks ISERROR(VLOOKUP), if true returns "", otherwise retruns VLOOKUP I have removed the $ around E12 so that you can copy down... "saltnsnails" wrote: It says I have too few arguments. :( "Sheeloo" wrote: instead of your vlookup put this in the cells in Excel 2003 =ISERROR(VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ),"",VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 )) in 2007 you can shorten that too =IFERROR('your VLOOKUP without quotes shown here',"") "saltnsnails" wrote: I have a cell that has a validation table that allows data to be entered that is not available in my "list". It brings up the information pop up window and you click ok to keep going with the information entered that is not on the list. Another cell has a vlookup table dependent on the data in the validation cell. I would like for the vlookup cell to return nothing if the validation cell had data entered on it that is not included on the validation list. If I put the last argument in the Vlookup, it returns an N/A #. My formula looks like this: =IF($E$120, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "") Any thoughts? Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup dependent on a validation table
Just a heads up, in these situations it is probably better to use ISNA
instead of ISERROR since ISERROR would shield errors that can be of interest whereas ISNA is only hiding #N/A -- Regards, Peo Sjoblom "Sheeloo" wrote in message ... Sorry, I forgot the IF statement... Try... =IF(ISERROR(VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE)),"",VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE )) bascially it checks ISERROR(VLOOKUP), if true returns "", otherwise retruns VLOOKUP I have removed the $ around E12 so that you can copy down... "saltnsnails" wrote: It says I have too few arguments. :( "Sheeloo" wrote: instead of your vlookup put this in the cells in Excel 2003 =ISERROR(VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ),"",VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 )) in 2007 you can shorten that too =IFERROR('your VLOOKUP without quotes shown here',"") "saltnsnails" wrote: I have a cell that has a validation table that allows data to be entered that is not available in my "list". It brings up the information pop up window and you click ok to keep going with the information entered that is not on the list. Another cell has a vlookup table dependent on the data in the validation cell. I would like for the vlookup cell to return nothing if the validation cell had data entered on it that is not included on the validation list. If I put the last argument in the Vlookup, it returns an N/A #. My formula looks like this: =IF($E$120, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "") Any thoughts? Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup dependent on a validation table
Thanks Peo,
That is a very good suggestion... I will keep that in mind. Regards, "Peo Sjoblom" wrote: Just a heads up, in these situations it is probably better to use ISNA instead of ISERROR since ISERROR would shield errors that can be of interest whereas ISNA is only hiding #N/A -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dependent DropLists - Using Validation | Excel Worksheet Functions | |||
| Validation of cells who is dependent on each other | Excel Discussion (Misc queries) | |||
| If, vlookup, data validation & dependent list | Excel Worksheet Functions | |||
| vlookup with validation table | Excel Discussion (Misc queries) | |||
| Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
Linear Mode
