=IF(ISERROR((vlookup($A$2:$A$31,
Sheet2!$A$2:$A$14, 1, 0)), "Not Active", ((vlookup($A$2:$A$31,
Sheet2!$A$2:$A$14, 1, 0))).
Im using this formula to take names from one sheet and display on another sheet but when I put the formula in B2 Im not getting any results. Can someone help me please?
Your Vlookup formula can only read from ONE lookup value (reference cell) at a time... hence your formula in B2:
=IF(ISERROR((vlookup($A$2:$A$31,Sheet2!$A$2:$A$14,1,0)), "Not Active",vlookup($A$2:$A$31,Sheet2!$A$2:$A$14,1,0))).
should be:
=IF(ISERROR((vlookup($A2,Sheet2!$A$2:$A$14,1,0)),"Not Active",((vlookup($A2,Sheet2!$A$2:$A$14,1,0))).
then copy this down the range B3:B31. Each cell in 'B' will return either the matching value from Sheet2 or the "Not Active" text.
Note the change in the vlookup reference from $A$2:$A$31 to $A2. As you copy this down, the range will change in B3 to $A3, in B4 to $A4, etc.
HTH
Bruce
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
For some reason Im having problems with the "Not Active" statement in the formula Im getting #value! in red as an error?
I think there is a syntax error in the original formula, copy and paste this into B2:
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not Active",(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)))
Does this work for you?
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not
Active",((VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"numerion" <numerion.1rpcav_1120576048.5616@excelforum-nospam.com> wrote in
message news:numerion.1rpcav_1120576048.5616@excelforum-nospam.com...
>
> For some reason Im having problems with the "Not Active" statement in
> the formula Im getting #value! in red as an error?
>
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile:
http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
>
And you have another response at your other thread in .programming.
numerion wrote:
>
> =IF(ISERROR((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0)), "Not Active", ((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0))).
>
> Im using this formula to take names from one sheet and display on
> another sheet but when I put the formula in B2 Im not getting any
> results. Can someone help me please?
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile: http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
--
Dave Peterson
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not
Active",((VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"numerion" <numerion.1rpcav_1120576048.5616@excelforum-nospam.com> wrote in
message news:numerion.1rpcav_1120576048.5616@excelforum-nospam.com...
>
> For some reason Im having problems with the "Not Active" statement in
> the formula Im getting #value! in red as an error?
>
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile:
http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
>
And you have another response at your other thread in .programming.
numerion wrote:
>
> =IF(ISERROR((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0)), "Not Active", ((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0))).
>
> Im using this formula to take names from one sheet and display on
> another sheet but when I put the formula in B2 Im not getting any
> results. Can someone help me please?
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile: http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
--
Dave Peterson
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not
Active",((VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"numerion" <numerion.1rpcav_1120576048.5616@excelforum-nospam.com> wrote in
message news:numerion.1rpcav_1120576048.5616@excelforum-nospam.com...
>
> For some reason Im having problems with the "Not Active" statement in
> the formula Im getting #value! in red as an error?
>
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile:
http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
>
And you have another response at your other thread in .programming.
numerion wrote:
>
> =IF(ISERROR((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0)), "Not Active", ((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0))).
>
> Im using this formula to take names from one sheet and display on
> another sheet but when I put the formula in B2 Im not getting any
> results. Can someone help me please?
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile: http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
--
Dave Peterson
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not
Active",((VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"numerion" <numerion.1rpcav_1120576048.5616@excelforum-nospam.com> wrote in
message news:numerion.1rpcav_1120576048.5616@excelforum-nospam.com...
>
> For some reason Im having problems with the "Not Active" statement in
> the formula Im getting #value! in red as an error?
>
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile:
http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
>
And you have another response at your other thread in .programming.
numerion wrote:
>
> =IF(ISERROR((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0)), "Not Active", ((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0))).
>
> Im using this formula to take names from one sheet and display on
> another sheet but when I put the formula in B2 Im not getting any
> results. Can someone help me please?
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile: http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
--
Dave Peterson
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not
Active",((VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"numerion" <numerion.1rpcav_1120576048.5616@excelforum-nospam.com> wrote in
message news:numerion.1rpcav_1120576048.5616@excelforum-nospam.com...
>
> For some reason Im having problems with the "Not Active" statement in
> the formula Im getting #value! in red as an error?
>
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile:
http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
>
And you have another response at your other thread in .programming.
numerion wrote:
>
> =IF(ISERROR((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0)), "Not Active", ((vlookup($A$2:$A$31,
> Sheet2!$A$2:$A$14, 1, 0))).
>
> Im using this formula to take names from one sheet and display on
> another sheet but when I put the formula in B2 Im not getting any
> results. Can someone help me please?
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile: http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
--
Dave Peterson
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0)),"Not
Active",((VLOOKUP($A2,Sheet2!$A$2:$A$14,1,0))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"numerion" <numerion.1rpcav_1120576048.5616@excelforum-nospam.com> wrote in
message news:numerion.1rpcav_1120576048.5616@excelforum-nospam.com...
>
> For some reason Im having problems with the "Not Active" statement in
> the formula Im getting #value! in red as an error?
>
>
> --
> numerion
> ------------------------------------------------------------------------
> numerion's Profile:
http://www.excelforum.com/member.php...o&userid=22975
> View this thread: http://www.excelforum.com/showthread...hreadid=384538
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks