+ Reply to Thread
Results 1 to 10 of 10

Vlookup

  1. #1
    SteveH
    Guest

    Re: Vlookup

    The lookup values need to be in the first column of the array, not the last.

    HTH

    Steve H


    "Rui" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"




  2. #2
    CLR
    Guest

    Re: Vlookup

    Your formula looks fine.........probably the reason you're getting the #N/A
    is because Excel is not finding your value in the lookup table........could
    be one is TEXT vs the other being NUMBERS..........

    From HELP:
    Remarks

    If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
    largest value that is less than or equal to lookup_value.


    If lookup_value is smaller than the smallest value in the first column of
    table_array, VLOOKUP returns the #N/A error value.


    If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
    returns the #N/A value.

    hth
    Vaya con Dios,
    Chuck, CABGx3




    "Rui" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"




  3. #3
    ΓΙΑΝΝΗΣ Χ.&Be
    Guest

    RE: Vlookup

    You want to make left VLOOKUP
    Try :
    =VLOOKUP(B1;CHOOSE({2;1};W100:W105;X100:X105);2;FALSE)

    Formula =CHOOSE({1;2;3;….};col1;col2;col3;…) return an array.
    So, you can make VLOOKUP between columns where ever they are, even in
    different sheets or books.
    For example:
    vlookup between 2 columns (columns no in same array):
    =VLOOKUP(xxxx;CHOOSE({1;2};A1:A100;D1:D100));2;0)

    Left vlookup:
    =VLOOKUP(xxxx;CHOOSE({2;1};A1:A100;B1:B100));2;0)

    vlookup between column and row:
    {=VLOOKUP(xxxx;CHOOSE({1;2};A4:A13;TRANSPOSE(A2:J2));2;0)}


    vlookup between 2 books:
    {=VLOOKUP(D1;CHOOSE({1;2};
    'C:\examples\[book1.xls]Sheet1'!$A1:$A2000;
    'C:\examples\[book2.xls]Sheet1'!$C100:$C2099);2;FALSE)}

    Ioannis Varlamis, Athens


    "Rui" wrote:

    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"


  4. #4
    SteveH
    Guest

    Re: Vlookup

    The lookup values need to be in the first column of the array, not the last.

    HTH

    Steve H


    "Rui" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"




  5. #5
    CLR
    Guest

    Re: Vlookup

    Your formula looks fine.........probably the reason you're getting the #N/A
    is because Excel is not finding your value in the lookup table........could
    be one is TEXT vs the other being NUMBERS..........

    From HELP:
    Remarks

    If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
    largest value that is less than or equal to lookup_value.


    If lookup_value is smaller than the smallest value in the first column of
    table_array, VLOOKUP returns the #N/A error value.


    If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
    returns the #N/A value.

    hth
    Vaya con Dios,
    Chuck, CABGx3




    "Rui" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"




  6. #6
    ΓΙΑΝΝΗΣ Χ.&Be
    Guest

    RE: Vlookup

    You want to make left VLOOKUP
    Try :
    =VLOOKUP(B1;CHOOSE({2;1};W100:W105;X100:X105);2;FALSE)

    Formula =CHOOSE({1;2;3;….};col1;col2;col3;…) return an array.
    So, you can make VLOOKUP between columns where ever they are, even in
    different sheets or books.
    For example:
    vlookup between 2 columns (columns no in same array):
    =VLOOKUP(xxxx;CHOOSE({1;2};A1:A100;D1:D100));2;0)

    Left vlookup:
    =VLOOKUP(xxxx;CHOOSE({2;1};A1:A100;B1:B100));2;0)

    vlookup between column and row:
    {=VLOOKUP(xxxx;CHOOSE({1;2};A4:A13;TRANSPOSE(A2:J2));2;0)}


    vlookup between 2 books:
    {=VLOOKUP(D1;CHOOSE({1;2};
    'C:\examples\[book1.xls]Sheet1'!$A1:$A2000;
    'C:\examples\[book2.xls]Sheet1'!$C100:$C2099);2;FALSE)}

    Ioannis Varlamis, Athens


    "Rui" wrote:

    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"


  7. #7
    Rui
    Guest

    Vlookup

    Hi,
    i want to do a vlookup, being my lookup value from a validation list.
    The answer is #N/A!
    Is there a resrtition to the use of this formula with validation date?

    heres the example in A1:

    =vlookup(b1;example;1;false), where b1 is a validated by the list
    "x100:x105" and "example=w100:x105"

  8. #8
    SteveH
    Guest

    Re: Vlookup

    The lookup values need to be in the first column of the array, not the last.

    HTH

    Steve H


    "Rui" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"




  9. #9
    CLR
    Guest

    Re: Vlookup

    Your formula looks fine.........probably the reason you're getting the #N/A
    is because Excel is not finding your value in the lookup table........could
    be one is TEXT vs the other being NUMBERS..........

    From HELP:
    Remarks

    If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
    largest value that is less than or equal to lookup_value.


    If lookup_value is smaller than the smallest value in the first column of
    table_array, VLOOKUP returns the #N/A error value.


    If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
    returns the #N/A value.

    hth
    Vaya con Dios,
    Chuck, CABGx3




    "Rui" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"




  10. #10
    ΓΙΑΝΝΗΣ Χ.&Be
    Guest

    RE: Vlookup

    You want to make left VLOOKUP
    Try :
    =VLOOKUP(B1;CHOOSE({2;1};W100:W105;X100:X105);2;FALSE)

    Formula =CHOOSE({1;2;3;….};col1;col2;col3;…) return an array.
    So, you can make VLOOKUP between columns where ever they are, even in
    different sheets or books.
    For example:
    vlookup between 2 columns (columns no in same array):
    =VLOOKUP(xxxx;CHOOSE({1;2};A1:A100;D1:D100));2;0)

    Left vlookup:
    =VLOOKUP(xxxx;CHOOSE({2;1};A1:A100;B1:B100));2;0)

    vlookup between column and row:
    {=VLOOKUP(xxxx;CHOOSE({1;2};A4:A13;TRANSPOSE(A2:J2));2;0)}


    vlookup between 2 books:
    {=VLOOKUP(D1;CHOOSE({1;2};
    'C:\examples\[book1.xls]Sheet1'!$A1:$A2000;
    'C:\examples\[book2.xls]Sheet1'!$C100:$C2099);2;FALSE)}

    Ioannis Varlamis, Athens


    "Rui" wrote:

    > Hi,
    > i want to do a vlookup, being my lookup value from a validation list.
    > The answer is #N/A!
    > Is there a resrtition to the use of this formula with validation date?
    >
    > heres the example in A1:
    >
    > =vlookup(b1;example;1;false), where b1 is a validated by the list
    > "x100:x105" and "example=w100:x105"


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1