|
|
|
|
|
|
|
|
|
|
| |
It works "as-is" on XL2k also.........
Vaya con Dios,
Chuck, CABGx3
"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
> Hi
> your formula should do exactly what you request and it works when i test
it
> ... if you're using excel 2002 or excel 2003 can you spot what is wrong if
> you click on the cell with the formula and use tools / formula auditing /
> evaluate formula to step through it?
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
> news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
> >I have a very large worksheet that I am using the following vlook up on
> >
> >
=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
,Dataentry!$D$9:$P$1508,12,0))
> > I am using the results of the lookup for another calculation, so I
don't
> > want the return value from my look up to be N/A. Can you please tell me
> > how
> > to make it 0 instead of the N/A when there is no match in the lookup
> > sheet.
> > Thanks
> >
>
>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
I have a similair problem except that I need to add two lookup values. How
do I write this formula if one or the other could be either zero or non
existant - possibly even both. Let's assume 1 Vlookup value is Z and another
is B. There are four possible situations and I probably need a nested if
stated (in the cell) but don't know how. Logically I want to say:
If (Z=TRUE) AND (B=TRUE) THEN "0"
ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
"CLR" wrote:
> It works "as-is" on XL2k also.........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
> > Hi
> > your formula should do exactly what you request and it works when i test
> it
> > ... if you're using excel 2002 or excel 2003 can you spot what is wrong if
> > you click on the cell with the formula and use tools / formula auditing /
> > evaluate formula to step through it?
> >
> > --
> > Cheers
> > JulieD
> > check out www.hcts.net.au/tipsandtricks.htm
> > ...well i'm working on it anyway
> > "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
> > >I have a very large worksheet that I am using the following vlook up on
> > >
> > >
> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
> ,Dataentry!$D$9:$P$1508,12,0))
> > > I am using the results of the lookup for another calculation, so I
> don't
> > > want the return value from my look up to be N/A. Can you please tell me
> > > how
> > > to make it 0 instead of the N/A when there is no match in the lookup
> > > sheet.
> > > Thanks
> > >
> >
> >
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi
something along these lines?????
=IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z=B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array,col_num,true_or_false)))
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JICDB" <JICDB@discussions.microsoft.com> wrote in message
news:38C1A72E-EA64-4296-AF77-4537B9385893@microsoft.com...
>I have a similair problem except that I need to add two lookup values. How
> do I write this formula if one or the other could be either zero or non
> existant - possibly even both. Let's assume 1 Vlookup value is Z and
> another
> is B. There are four possible situations and I probably need a nested if
> stated (in the cell) but don't know how. Logically I want to say:
>
> If (Z=TRUE) AND (B=TRUE) THEN "0"
> ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
> ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
> ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
>
> "CLR" wrote:
>
>> It works "as-is" on XL2k also.........
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
>> news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
>> > Hi
>> > your formula should do exactly what you request and it works when i
>> > test
>> it
>> > ... if you're using excel 2002 or excel 2003 can you spot what is wrong
>> > if
>> > you click on the cell with the formula and use tools / formula auditing
>> > /
>> > evaluate formula to step through it?
>> >
>> > --
>> > Cheers
>> > JulieD
>> > check out www.hcts.net.au/tipsandtricks.htm
>> > ...well i'm working on it anyway
>> > "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
>> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
>> > >I have a very large worksheet that I am using the following vlook up
>> > >on
>> > >
>> > >
>> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
>> ,Dataentry!$D$9:$P$1508,12,0))
>> > > I am using the results of the lookup for another calculation, so I
>> don't
>> > > want the return value from my look up to be N/A. Can you please tell
>> > > me
>> > > how
>> > > to make it 0 instead of the N/A when there is no match in the lookup
>> > > sheet.
>> > > Thanks
>> > >
>> >
>> >
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Thanks JulieD - I'll try it after I return from lunch. I was so frustrated
that I temporarily gave up. You've given me hope to try again.
"JulieD" wrote:
> Hi
>
> something along these lines?????
>
> =IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z=B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array,col_num,true_or_false)))
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "JICDB" <JICDB@discussions.microsoft.com> wrote in message
> news:38C1A72E-EA64-4296-AF77-4537B9385893@microsoft.com...
> >I have a similair problem except that I need to add two lookup values. How
> > do I write this formula if one or the other could be either zero or non
> > existant - possibly even both. Let's assume 1 Vlookup value is Z and
> > another
> > is B. There are four possible situations and I probably need a nested if
> > stated (in the cell) but don't know how. Logically I want to say:
> >
> > If (Z=TRUE) AND (B=TRUE) THEN "0"
> > ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
> > ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
> > ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
> >
> > "CLR" wrote:
> >
> >> It works "as-is" on XL2k also.........
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >>
> >> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> >> news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
> >> > Hi
> >> > your formula should do exactly what you request and it works when i
> >> > test
> >> it
> >> > ... if you're using excel 2002 or excel 2003 can you spot what is wrong
> >> > if
> >> > you click on the cell with the formula and use tools / formula auditing
> >> > /
> >> > evaluate formula to step through it?
> >> >
> >> > --
> >> > Cheers
> >> > JulieD
> >> > check out www.hcts.net.au/tipsandtricks.htm
> >> > ...well i'm working on it anyway
> >> > "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
> >> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
> >> > >I have a very large worksheet that I am using the following vlook up
> >> > >on
> >> > >
> >> > >
> >> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
> >> ,Dataentry!$D$9:$P$1508,12,0))
> >> > > I am using the results of the lookup for another calculation, so I
> >> don't
> >> > > want the return value from my look up to be N/A. Can you please tell
> >> > > me
> >> > > how
> >> > > to make it 0 instead of the N/A when there is no match in the lookup
> >> > > sheet.
> >> > > Thanks
> >> > >
> >> >
> >> >
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Try using =IF(ISERROR(VLOOKUP(TRIM($G12),
instead of (ISNA(Vlookup(Trim$g$12
That took the N/A out of my worksheet. Hope this works for you.
JCI
JICDB" wrote:
> Thanks JulieD - I'll try it after I return from lunch. I was so frustrated
> that I temporarily gave up. You've given me hope to try again.
>
> "JulieD" wrote:
>
> > Hi
> >
> > something along these lines?????
> >
> > =IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z=B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array,col_num,true_or_false)))
> >
> > --
> > Cheers
> > JulieD
> > check out www.hcts.net.au/tipsandtricks.htm
> > ....well i'm working on it anyway
> > "JICDB" <JICDB@discussions.microsoft.com> wrote in message
> > news:38C1A72E-EA64-4296-AF77-4537B9385893@microsoft.com...
> > >I have a similair problem except that I need to add two lookup values. How
> > > do I write this formula if one or the other could be either zero or non
> > > existant - possibly even both. Let's assume 1 Vlookup value is Z and
> > > another
> > > is B. There are four possible situations and I probably need a nested if
> > > stated (in the cell) but don't know how. Logically I want to say:
> > >
> > > If (Z=TRUE) AND (B=TRUE) THEN "0"
> > > ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
> > > ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
> > > ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
> > >
> > > "CLR" wrote:
> > >
> > >> It works "as-is" on XL2k also.........
> > >>
> > >> Vaya con Dios,
> > >> Chuck, CABGx3
> > >>
> > >>
> > >> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> > >> news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
> > >> > Hi
> > >> > your formula should do exactly what you request and it works when i
> > >> > test
> > >> it
> > >> > ... if you're using excel 2002 or excel 2003 can you spot what is wrong
> > >> > if
> > >> > you click on the cell with the formula and use tools / formula auditing
> > >> > /
> > >> > evaluate formula to step through it?
> > >> >
> > >> > --
> > >> > Cheers
> > >> > JulieD
> > >> > check out www.hcts.net.au/tipsandtricks.htm
> > >> > ...well i'm working on it anyway
> > >> > "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
> > >> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
> > >> > >I have a very large worksheet that I am using the following vlook up
> > >> > >on
> > >> > >
> > >> > >
> > >> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
> > >> ,Dataentry!$D$9:$P$1508,12,0))
> > >> > > I am using the results of the lookup for another calculation, so I
> > >> don't
> > >> > > want the return value from my look up to be N/A. Can you please tell
> > >> > > me
> > >> > > how
> > >> > > to make it 0 instead of the N/A when there is no match in the lookup
> > >> > > sheet.
> > >> > > Thanks
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I understand all but the last section that begins with "table" Could you
tell me how this section should relate to my data?
"JulieD" wrote:
> Hi
>
> something along these lines?????
>
> =IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z=B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array,col_num,true_or_false)))
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "JICDB" <JICDB@discussions.microsoft.com> wrote in message
> news:38C1A72E-EA64-4296-AF77-4537B9385893@microsoft.com...
> >I have a similair problem except that I need to add two lookup values. How
> > do I write this formula if one or the other could be either zero or non
> > existant - possibly even both. Let's assume 1 Vlookup value is Z and
> > another
> > is B. There are four possible situations and I probably need a nested if
> > stated (in the cell) but don't know how. Logically I want to say:
> >
> > If (Z=TRUE) AND (B=TRUE) THEN "0"
> > ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
> > ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
> > ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
> >
> > "CLR" wrote:
> >
> >> It works "as-is" on XL2k also.........
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >>
> >> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> >> news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
> >> > Hi
> >> > your formula should do exactly what you request and it works when i
> >> > test
> >> it
> >> > ... if you're using excel 2002 or excel 2003 can you spot what is wrong
> >> > if
> >> > you click on the cell with the formula and use tools / formula auditing
> >> > /
> >> > evaluate formula to step through it?
> >> >
> >> > --
> >> > Cheers
> >> > JulieD
> >> > check out www.hcts.net.au/tipsandtricks.htm
> >> > ...well i'm working on it anyway
> >> > "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
> >> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
> >> > >I have a very large worksheet that I am using the following vlook up
> >> > >on
> >> > >
> >> > >
> >> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
> >> ,Dataentry!$D$9:$P$1508,12,0))
> >> > > I am using the results of the lookup for another calculation, so I
> >> don't
> >> > > want the return value from my look up to be N/A. Can you please tell
> >> > > me
> >> > > how
> >> > > to make it 0 instead of the N/A when there is no match in the lookup
> >> > > sheet.
> >> > > Thanks
> >> > >
> >> >
> >> >
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi
i understood that you were wanting to use the VLOOKUP function with the
possibility of two lookup values Z & B
when you said
---
Let's assume 1 Vlookup value is Z and another is B.
----
the VLOOKUP function as four parameters
=VLOOKUP(lookup_value, table_array,col_#_of_data_to_return,approx_match)
the lookup_value is either Z or B depending on the result of the IF
statement
however, you still need to fill in the rest of the VLOOKUP statement
if this doesn't make sense, please try explaining what you're after again
using the "real" data that you have because i found the Z & B concept a bit
difficult to comprehend.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JICDB" <JICDB@discussions.microsoft.com> wrote in message
news:F4AB80C4-D126-4748-BE5B-C8B9B85F0C3F@microsoft.com...
>I understand all but the last section that begins with "table" Could you
> tell me how this section should relate to my data?
>
> "JulieD" wrote:
>
>> Hi
>>
>> something along these lines?????
>>
>> =IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z=B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array,col_num,true_or_false)))
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "JICDB" <JICDB@discussions.microsoft.com> wrote in message
>> news:38C1A72E-EA64-4296-AF77-4537B9385893@microsoft.com...
>> >I have a similair problem except that I need to add two lookup values.
>> >How
>> > do I write this formula if one or the other could be either zero or non
>> > existant - possibly even both. Let's assume 1 Vlookup value is Z and
>> > another
>> > is B. There are four possible situations and I probably need a nested
>> > if
>> > stated (in the cell) but don't know how. Logically I want to say:
>> >
>> > If (Z=TRUE) AND (B=TRUE) THEN "0"
>> > ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
>> > ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
>> > ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)
>> >
>> > "CLR" wrote:
>> >
>> >> It works "as-is" on XL2k also.........
>> >>
>> >> Vaya con Dios,
>> >> Chuck, CABGx3
>> >>
>> >>
>> >> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
>> >> news:#gRc$6DQFHA.1500@TK2MSFTNGP09.phx.gbl...
>> >> > Hi
>> >> > your formula should do exactly what you request and it works when i
>> >> > test
>> >> it
>> >> > ... if you're using excel 2002 or excel 2003 can you spot what is
>> >> > wrong
>> >> > if
>> >> > you click on the cell with the formula and use tools / formula
>> >> > auditing
>> >> > /
>> >> > evaluate formula to step through it?
>> >> >
>> >> > --
>> >> > Cheers
>> >> > JulieD
>> >> > check out www.hcts.net.au/tipsandtricks.htm
>> >> > ...well i'm working on it anyway
>> >> > "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
>> >> > news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
>> >> > >I have a very large worksheet that I am using the following vlook
>> >> > >up
>> >> > >on
>> >> > >
>> >> > >
>> >> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5)
>> >> ,Dataentry!$D$9:$P$1508,12,0))
>> >> > > I am using the results of the lookup for another calculation, so
>> >> > > I
>> >> don't
>> >> > > want the return value from my look up to be N/A. Can you please
>> >> > > tell
>> >> > > me
>> >> > > how
>> >> > > to make it 0 instead of the N/A when there is no match in the
>> >> > > lookup
>> >> > > sheet.
>> >> > > Thanks
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi,
I am dealing with the same problem however, the formula is not working for
me (office 2007). The formula I use is:
=VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE)
I made out of this
=if(isna(VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE),0,VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE))
Now I get a message the the formula contains an error...
What is wrong?
"JulieD" wrote:
> Hi
> your formula should do exactly what you request and it works when i test it
> .... if you're using excel 2002 or excel 2003 can you spot what is wrong if
> you click on the cell with the formula and use tools / formula auditing /
> evaluate formula to step through it?
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
> news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
> >I have a very large worksheet that I am using the following vlook up on
> >
> > =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0))
> > I am using the results of the lookup for another calculation, so I don't
> > want the return value from my look up to be N/A. Can you please tell me
> > how
> > to make it 0 instead of the N/A when there is no match in the lookup
> > sheet.
> > Thanks
> >
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi
=IF(ISNA(VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE));0;VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE))
Regards,
Per
"Vincent" <Vincent@discussions.microsoft.com> skrev i meddelelsen
news:FB7B3007-F994-49E6-9F09-60868285BA8B@microsoft.com...
> Hi,
>
> I am dealing with the same problem however, the formula is not working for
> me (office 2007). The formula I use is:
> =VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE)
> I made out of this
> =if(isna(VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE),0,VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE))
>
> Now I get a message the the formula contains an error...
>
> What is wrong?
>
>
>
> "JulieD" wrote:
>
>> Hi
>> your formula should do exactly what you request and it works when i test
>> it
>> .... if you're using excel 2002 or excel 2003 can you spot what is wrong
>> if
>> you click on the cell with the formula and use tools / formula auditing /
>> evaluate formula to step through it?
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "JeaneIsaac" <JeaneIsaac@discussions.microsoft.com> wrote in message
>> news:2AAC17D3-7112-48CF-B795-FD53EA0357C3@microsoft.com...
>> >I have a very large worksheet that I am using the following vlook up on
>> >
>> > =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0))
>> > I am using the results of the lookup for another calculation, so I
>> > don't
>> > want the return value from my look up to be N/A. Can you please tell
>> > me
>> > how
>> > to make it 0 instead of the N/A when there is no match in the lookup
>> > sheet.
>> > Thanks
>> >
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Works A1 here - excel 2003
=IF(ISNA(VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FALSE)), "",
VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FALSE))
I wanted "nothing" instead of 0.
"JeaneIsaac" wrote:
> I have a very large worksheet that I am using the following vlook up on
>
> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0))
> I am using the results of the lookup for another calculation, so I don't
> want the return value from my look up to be N/A. Can you please tell me how
> to make it 0 instead of the N/A when there is no match in the lookup sheet.
> Thanks
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
You can use ISERROR like this:
=IF(ISERROR(A1/B1),"",A1/B1)
MathieuM;319393 Wrote:
> Works A1 here - excel 2003
>
> =IF(ISNA(VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FALSE)), "",
> VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FALSE))
>
> I wanted "nothing" instead of 0.
>
> "JeaneIsaac" wrote:
>
> > I have a very large worksheet that I am using the following vlook up
> on
> >
> >
> =IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508,12,0))
> > I am using the results of the lookup for another calculation, so I
> don't
> > want the return value from my look up to be N/A. Can you please tell
> me how
> > to make it 0 instead of the N/A when there is no match in the lookup
> sheet.
> > Thanks
> >
--
Simon Lloyd
Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=89297
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
Question
Answer
Notify me of replies