| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help in Simple Lookup Function
=IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable")
hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
It's a formula error. The correct formula is:
=IF(ISERROR(VLOOKUP($E$2,L:O,4,FALSE)), "Not Available") Although presumably you want to return the value from VLOOKUP if it's not an error, so.. =IF(ISERROR(VLOOKUP($E$2,L:O,4,FALSE)), "Not Available",VLOOKUP($E$2,L:O,4,FALSE)) "Raz" wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
this prints FALSE in the cell
"Sean Timmons" wrote: It's a formula error. The correct formula is: =IF(ISERROR(VLOOKUP($E$2,L:O,4,FALSE)), "Not Available") Although presumably you want to return the value from VLOOKUP if it's not an error, so.. =IF(ISERROR(VLOOKUP($E$2,L:O,4,FALSE)), "Not Available",VLOOKUP($E$2,L:O,4,FALSE)) "Raz" wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
Please see the 2nd part of my post. the first would return false if there is
no error. Thus, use the vlookup in the false section of your IF statement. "Sean Timmons" wrote: It's a formula error. The correct formula is: =IF(ISERROR(VLOOKUP($E$2,L:O,4,FALSE)), "Not Available") Although presumably you want to return the value from VLOOKUP if it's not an error, so.. =IF(ISERROR(VLOOKUP($E$2,L:O,4,FALSE)), "Not Available",VLOOKUP($E$2,L:O,4,FALSE)) "Raz" wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
=if(iserror((your_formula),"not available",your_formula)
On Oct 7, 8:40*pm, Raz wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
sorry, its still returning 0
"muddan madhu" wrote: =if(iserror((your_formula),"not available",your_formula) On Oct 7, 8:40 pm, Raz wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
Muddhan just missed a ) after the first your_formula... add that and you have
it. "Raz" wrote: sorry, its still returning 0 "muddan madhu" wrote: =if(iserror((your_formula),"not available",your_formula) On Oct 7, 8:40 pm, Raz wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
=IF(ISERROR(VLOOKUP($E$2,L:Q,5,FALSE)),"Not
Available",VLOOKUP($E$2,L:Q,5,FALSE)) this is what i am using, as u suggested, but its returning 0 instead of "not available" "muddan madhu" wrote: =if(iserror((your_formula),"not available",your_formula) On Oct 7, 8:40 pm, Raz wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
That would be due to the matching value in your P column is either blank or
the value 0. "Raz" wrote: =IF(ISERROR(VLOOKUP($E$2,L:Q,5,FALSE)),"Not Available",VLOOKUP($E$2,L:Q,5,FALSE)) this is what i am using, as u suggested, but its returning 0 instead of "not available" "muddan madhu" wrote: =if(iserror((your_formula),"not available",your_formula) On Oct 7, 8:40 pm, Raz wrote: =IFERROR(VLOOKUP($E$2,L:O,4,FALSE), "Not Avialable") hi, I am using this (above) equation, to do lookups from 5 columns. the lookup thing is working but when there is nothing in the cell (that the function looking up) it returns 0 instead of "Not Available" (as in the equation). i want the function to show "Not available" when the cell is blank, or there is an error. thanks in advance |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
well that is my whole purpose here,
i want equation to return "not available" if the cell is blank. it is returning 0, and i need to fix that, everything else is working fine. thanks again. "Sean Timmons" wrote: That would be due to the matching value in your P column is either blank or the value 0. "Raz" wrote: =IF(ISERROR(VLOOKUP($E$2,L:Q,5,FALSE)),"Not Available",VLOOKUP($E$2,L:Q,5,FALSE)) this is what i am using, as u suggested, but its returning 0 instead of "not available" |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help in Simple Lookup Function
Easy enough..
=IF(VLOOKUP($E$2,L:Q,5,FALSE)=0,"Not Available",VLOOKUP($E$2,L:Q,5,FALSE)) If you want both errors and 0 to be captured: =IF(OR(ISERROR(VLOOKUP($E$2,L:Q,5,FALSE)),VLOOKUP( $E$2,L:Q,5,FALSE)=0),"Not Available",VLOOKUP($E$2,L:Q,5,FALSE)) "Raz" wrote: well that is my whole purpose here, i want equation to return "not available" if the cell is blank. it is returning 0, and i need to fix that, everything else is working fine. thanks again. "Sean Timmons" wrote: That would be due to the matching value in your P column is either blank or the value 0. "Raz" wrote: =IF(ISERROR(VLOOKUP($E$2,L:Q,5,FALSE)),"Not Available",VLOOKUP($E$2,L:Q,5,FALSE)) this is what i am using, as u suggested, but its returning 0 instead of "not available" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
| Can anyone help with a Simple Lookup please? | Excel Discussion (Misc queries) | |||
| Simple lookup - or maybe not so simple - help! | Excel Worksheet Functions | |||
| simple lookup | Excel Discussion (Misc queries) | |||
| Lookup help (simple) | Excel Worksheet Functions | |||
Hybrid Mode
