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

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

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

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

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

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

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

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

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

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"

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"

Need Help in Simple Lookup Function
 

I guess I am not making sense here, or you are not getting my problem.

So, i will explain the situation a little more.

here is how my workseet looks.

A B L M
1 John 400 John 400
2 Mack 500

I have the equation/function in B1, when i type John in A1 the function
should lookup in the columns L and M for 'John' and corresponding number in
Column M.
Then the function should return 400 (this case). But if the M1 is blank,
then the function should return "Not Available" in B2.

I hope this make sense, all the previous equations were returning 0, where I
want it to return "Not available" if M1 is blank.

Need Help in Simple Lookup Function
 

=if(isna(vlookup(...)),"not available",if(vlookup(...)="","Not available",
vlookup(...)))

or

=if(iserror(1/len(vlookup(...))),"not available",vlookup(...))



Raz wrote:

I guess I am not making sense here, or you are not getting my problem.

So, i will explain the situation a little more.

here is how my workseet looks.

A B L M
1 John 400 John 400
2 Mack 500

I have the equation/function in B1, when i type John in A1 the function
should lookup in the columns L and M for 'John' and corresponding number in
Column M.
Then the function should return 400 (this case). But if the M1 is blank,
then the function should return "Not Available" in B2.

I hope this make sense, all the previous equations were returning 0, where I
want it to return "Not available" if M1 is blank.

--

Dave Peterson

Need Help in Simple Lookup Function
 

thanks Dave, it worked.

but i would like to know what is 1/len
and what is isna
would you please explain, thanks




"Dave Peterson" wrote:

=if(isna(vlookup(...)),"not available",if(vlookup(...)="","Not available",
vlookup(...)))

or

=if(iserror(1/len(vlookup(...))),"not available",vlookup(...))



Raz wrote:

I guess I am not making sense here, or you are not getting my problem.

So, i will explain the situation a little more.

here is how my workseet looks.

A B L M
1 John 400 John 400
2 Mack 500

I have the equation/function in B1, when i type John in A1 the function
should lookup in the columns L and M for 'John' and corresponding number in
Column M.
Then the function should return 400 (this case). But if the M1 is blank,
then the function should return "Not Available" in B2.

I hope this make sense, all the previous equations were returning 0, where I
want it to return "Not available" if M1 is blank.

--

Dave Peterson

Need Help in Simple Lookup Function
 

If there is not match in the first column of the lookup table, your =vlookup()
formula will return #n/a. The =if(isna(...), ...
looks to see if that error is returned.

Your =vlookup() formula could return a few things:
A value (like 400 or 500 in your example)
An empty string ("") if the "sending" cell was empty.
or that #n/a error.

If the value is returned then
=1/len(500)
won't cause an error.

If that empty string ("") is returned, then this:
=1/len("")
is the same as
=1/0
which will cause a division by 0 error (#div/0!)

If the #n/a error is returned then:
=1(len(#n/a))
will cause an error (since it's not numeric).

So in either of the last two cases, "" or #n/a, an error would have been
returned.



Raz wrote:

thanks Dave, it worked.

but i would like to know what is 1/len
and what is isna
would you please explain, thanks

"Dave Peterson" wrote:

=if(isna(vlookup(...)),"not available",if(vlookup(...)="","Not available",
vlookup(...)))

or

=if(iserror(1/len(vlookup(...))),"not available",vlookup(...))



Raz wrote:

I guess I am not making sense here, or you are not getting my problem.

So, i will explain the situation a little more.

here is how my workseet looks.

A B L M
1 John 400 John 400
2 Mack 500

I have the equation/function in B1, when i type John in A1 the function
should lookup in the columns L and M for 'John' and corresponding number in
Column M.
Then the function should return 400 (this case). But if the M1 is blank,
then the function should return "Not Available" in B2.

I hope this make sense, all the previous equations were returning 0, where I
want it to return "Not available" if M1 is blank.

--

Dave Peterson


--

Dave Peterson