+ Reply to Thread
Results 1 to 20 of 20

multiple ranges on Vlookup

Hybrid View

  1. #1
    lpj
    Guest

    multiple ranges on Vlookup

    I currently have my Vlookup stmnt as this:

    =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))

    'Code Decrip' is the name of the worksheet
    I need to add another range X$3:Y48
    What is the proper syntax - I wasnt able to get it right after searching
    online for it.
    Thanks so much.

  2. #2
    Kassie
    Guest

    RE: multiple ranges on Vlookup

    Hi lpj

    Not quite clear what you want to achieve here?

    Why not have everything in the same range?

    Am I correct in guessing that, if you do not find a matching record in the
    primary range, you then want to do a lookup in the secondary range? If so,
    you will have to use an If(OR( statement, to first look at the primary range,
    and then, if you do not find anything there, do a VLOOKUP in the secondary
    range. You will therefore have to test the primary range for an error
    condition, and if the error condition exists, then look at the secondary
    range, else look at the primary range. Again, why not put the whole lot in
    one range?

    --
    j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa


    "lpj" wrote:

    > I currently have my Vlookup stmnt as this:
    >
    > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    >
    > 'Code Decrip' is the name of the worksheet
    > I need to add another range X$3:Y48
    > What is the proper syntax - I wasnt able to get it right after searching
    > online for it.
    > Thanks so much.


  3. #3
    lpj
    Guest

    RE: multiple ranges on Vlookup

    Thanks for the reply! The reason I can't put it all in the same range is bc
    this is an existing worksheet (which can't be modified) and there are some
    columns of data in btwn that shouldn't be referrenced - they could create
    duplicates or give invalid results back.

    "Kassie" wrote:

    > Hi lpj
    >
    > Not quite clear what you want to achieve here?
    >
    > Why not have everything in the same range?
    >
    > Am I correct in guessing that, if you do not find a matching record in the
    > primary range, you then want to do a lookup in the secondary range? If so,
    > you will have to use an If(OR( statement, to first look at the primary range,
    > and then, if you do not find anything there, do a VLOOKUP in the secondary
    > range. You will therefore have to test the primary range for an error
    > condition, and if the error condition exists, then look at the secondary
    > range, else look at the primary range. Again, why not put the whole lot in
    > one range?
    >
    > --
    > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    >
    >
    > "lpj" wrote:
    >
    > > I currently have my Vlookup stmnt as this:
    > >
    > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > >
    > > 'Code Decrip' is the name of the worksheet
    > > I need to add another range X$3:Y48
    > > What is the proper syntax - I wasnt able to get it right after searching
    > > online for it.
    > > Thanks so much.


  4. #4
    Kassie
    Guest

    RE: multiple ranges on Vlookup

    Hi lpj

    Try the following formula:

    =IF(E2="","",IF(ISERROR(VLOOKUP('Code
    Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
    Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))

    --
    j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa


    "lpj" wrote:

    > Thanks for the reply! The reason I can't put it all in the same range is bc
    > this is an existing worksheet (which can't be modified) and there are some
    > columns of data in btwn that shouldn't be referrenced - they could create
    > duplicates or give invalid results back.
    >
    > "Kassie" wrote:
    >
    > > Hi lpj
    > >
    > > Not quite clear what you want to achieve here?
    > >
    > > Why not have everything in the same range?
    > >
    > > Am I correct in guessing that, if you do not find a matching record in the
    > > primary range, you then want to do a lookup in the secondary range? If so,
    > > you will have to use an If(OR( statement, to first look at the primary range,
    > > and then, if you do not find anything there, do a VLOOKUP in the secondary
    > > range. You will therefore have to test the primary range for an error
    > > condition, and if the error condition exists, then look at the secondary
    > > range, else look at the primary range. Again, why not put the whole lot in
    > > one range?
    > >
    > > --
    > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    > >
    > >
    > > "lpj" wrote:
    > >
    > > > I currently have my Vlookup stmnt as this:
    > > >
    > > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > > >
    > > > 'Code Decrip' is the name of the worksheet
    > > > I need to add another range X$3:Y48
    > > > What is the proper syntax - I wasnt able to get it right after searching
    > > > online for it.
    > > > Thanks so much.


  5. #5
    lpj
    Guest

    RE: multiple ranges on Vlookup

    Thanks so much - to all of you! I really appreciate it!

    "Kassie" wrote:

    > Hi lpj
    >
    > Try the following formula:
    >
    > =IF(E2="","",IF(ISERROR(VLOOKUP('Code
    > Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
    > Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    >
    > --
    > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    >
    >
    > "lpj" wrote:
    >
    > > Thanks for the reply! The reason I can't put it all in the same range is bc
    > > this is an existing worksheet (which can't be modified) and there are some
    > > columns of data in btwn that shouldn't be referrenced - they could create
    > > duplicates or give invalid results back.
    > >
    > > "Kassie" wrote:
    > >
    > > > Hi lpj
    > > >
    > > > Not quite clear what you want to achieve here?
    > > >
    > > > Why not have everything in the same range?
    > > >
    > > > Am I correct in guessing that, if you do not find a matching record in the
    > > > primary range, you then want to do a lookup in the secondary range? If so,
    > > > you will have to use an If(OR( statement, to first look at the primary range,
    > > > and then, if you do not find anything there, do a VLOOKUP in the secondary
    > > > range. You will therefore have to test the primary range for an error
    > > > condition, and if the error condition exists, then look at the secondary
    > > > range, else look at the primary range. Again, why not put the whole lot in
    > > > one range?
    > > >
    > > > --
    > > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    > > >
    > > >
    > > > "lpj" wrote:
    > > >
    > > > > I currently have my Vlookup stmnt as this:
    > > > >
    > > > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > > > >
    > > > > 'Code Decrip' is the name of the worksheet
    > > > > I need to add another range X$3:Y48
    > > > > What is the proper syntax - I wasnt able to get it right after searching
    > > > > online for it.
    > > > > Thanks so much.


  6. #6
    lpj
    Guest

    RE: multiple ranges on Vlookup

    How would i nest another 1 or 2 ranges in there? I'm looking at this syntax
    but not getting very far - can i use 'or' operators? thanks again for your
    help!


    "Kassie" wrote:

    > Hi lpj
    >
    > Try the following formula:
    >
    > =IF(E2="","",IF(ISERROR(VLOOKUP('Code
    > Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
    > Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    >
    > --
    > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    >
    >
    > "lpj" wrote:
    >
    > > Thanks for the reply! The reason I can't put it all in the same range is bc
    > > this is an existing worksheet (which can't be modified) and there are some
    > > columns of data in btwn that shouldn't be referrenced - they could create
    > > duplicates or give invalid results back.
    > >
    > > "Kassie" wrote:
    > >
    > > > Hi lpj
    > > >
    > > > Not quite clear what you want to achieve here?
    > > >
    > > > Why not have everything in the same range?
    > > >
    > > > Am I correct in guessing that, if you do not find a matching record in the
    > > > primary range, you then want to do a lookup in the secondary range? If so,
    > > > you will have to use an If(OR( statement, to first look at the primary range,
    > > > and then, if you do not find anything there, do a VLOOKUP in the secondary
    > > > range. You will therefore have to test the primary range for an error
    > > > condition, and if the error condition exists, then look at the secondary
    > > > range, else look at the primary range. Again, why not put the whole lot in
    > > > one range?
    > > >
    > > > --
    > > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    > > >
    > > >
    > > > "lpj" wrote:
    > > >
    > > > > I currently have my Vlookup stmnt as this:
    > > > >
    > > > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > > > >
    > > > > 'Code Decrip' is the name of the worksheet
    > > > > I need to add another range X$3:Y48
    > > > > What is the proper syntax - I wasnt able to get it right after searching
    > > > > online for it.
    > > > > Thanks so much.


  7. #7
    Domenic
    Guest

    Re: multiple ranges on Vlookup

    Assuming that T3:U27, X3:Y48, AB3:AC27, and AF3:AG48 contain your
    tables, try...

    =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,8,12}
    ,{25,46,25,46}),E2)>0,0),T3:U27,X3:Y48,AB3:AC27,AF3:AG48),2,0),"")

    ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that the
    first array constant...

    {0,4,8,12}

    ....determines the number of columns to move right from Column T for each
    table, and the second array constant...

    {25,46,25,46}

    ....determines the number of rows contained in each table. Change these
    accordingly. Post back if you need further help...

    In article <F3EC6E53-157C-4985-B6A0-2965BAF94100@microsoft.com>,
    lpj <lpj@discussions.microsoft.com> wrote:

    > How would i nest another 1 or 2 ranges in there? I'm looking at this syntax
    > but not getting very far - can i use 'or' operators? thanks again for your
    > help!
    >
    >
    > "Kassie" wrote:
    >
    > > Hi lpj
    > >
    > > Try the following formula:
    > >
    > > =IF(E2="","",IF(ISERROR(VLOOKUP('Code
    > > Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
    > > Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > >
    > > --
    > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South
    > > Africa
    > >
    > >
    > > "lpj" wrote:
    > >
    > > > Thanks for the reply! The reason I can't put it all in the same range is
    > > > bc
    > > > this is an existing worksheet (which can't be modified) and there are
    > > > some
    > > > columns of data in btwn that shouldn't be referrenced - they could create
    > > > duplicates or give invalid results back.
    > > >
    > > > "Kassie" wrote:
    > > >
    > > > > Hi lpj
    > > > >
    > > > > Not quite clear what you want to achieve here?
    > > > >
    > > > > Why not have everything in the same range?
    > > > >
    > > > > Am I correct in guessing that, if you do not find a matching record in
    > > > > the
    > > > > primary range, you then want to do a lookup in the secondary range? If
    > > > > so,
    > > > > you will have to use an If(OR( statement, to first look at the primary
    > > > > range,
    > > > > and then, if you do not find anything there, do a VLOOKUP in the
    > > > > secondary
    > > > > range. You will therefore have to test the primary range for an error
    > > > > condition, and if the error condition exists, then look at the
    > > > > secondary
    > > > > range, else look at the primary range. Again, why not put the whole
    > > > > lot in
    > > > > one range?
    > > > >
    > > > > --
    > > > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South
    > > > > Africa
    > > > >
    > > > >
    > > > > "lpj" wrote:
    > > > >
    > > > > > I currently have my Vlookup stmnt as this:
    > > > > >
    > > > > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > > > > >
    > > > > > 'Code Decrip' is the name of the worksheet
    > > > > > I need to add another range X$3:Y48
    > > > > > What is the proper syntax - I wasnt able to get it right after
    > > > > > searching
    > > > > > online for it.
    > > > > > Thanks so much.


  8. #8
    lpj
    Guest

    RE: multiple ranges on Vlookup

    Duplicates or invalid values - to say the value of the lookup field could
    exist in 2 cells (i.e. T3 and V3, I would only want the returned value for
    T3 NOT V3). that's why i can't have an one entire range


    "Kassie" wrote:

    > Hi lpj
    >
    > Not quite clear what you want to achieve here?
    >
    > Why not have everything in the same range?
    >
    > Am I correct in guessing that, if you do not find a matching record in the
    > primary range, you then want to do a lookup in the secondary range? If so,
    > you will have to use an If(OR( statement, to first look at the primary range,
    > and then, if you do not find anything there, do a VLOOKUP in the secondary
    > range. You will therefore have to test the primary range for an error
    > condition, and if the error condition exists, then look at the secondary
    > range, else look at the primary range. Again, why not put the whole lot in
    > one range?
    >
    > --
    > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa
    >
    >
    > "lpj" wrote:
    >
    > > I currently have my Vlookup stmnt as this:
    > >
    > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > >
    > > 'Code Decrip' is the name of the worksheet
    > > I need to add another range X$3:Y48
    > > What is the proper syntax - I wasnt able to get it right after searching
    > > online for it.
    > > Thanks so much.


  9. #9
    Domenic
    Guest

    Re: multiple ranges on Vlookup

    Try...

    =IF(E2<>"",VLOOKUP(E2,IF(ISNUMBER(MATCH(E2,'Code
    Descrip'!T3:T27,0)),'Code Descrip'!T3:U27,'Code Descrip'!X3:Y48),2,0),"")

    Hope this helps!

    In article <8ECDC487-CF58-4BB2-97ED-C2C5B655575D@microsoft.com>,
    lpj <lpj@discussions.microsoft.com> wrote:

    > I currently have my Vlookup stmnt as this:
    >
    > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    >
    > 'Code Decrip' is the name of the worksheet
    > I need to add another range X$3:Y48
    > What is the proper syntax - I wasnt able to get it right after searching
    > online for it.
    > Thanks so much.


  10. #10
    lpj
    Guest

    Re: multiple ranges on Vlookup

    Thanks so much - to all of you! I really appreciate it!

    "Domenic" wrote:

    > Try...
    >
    > =IF(E2<>"",VLOOKUP(E2,IF(ISNUMBER(MATCH(E2,'Code
    > Descrip'!T3:T27,0)),'Code Descrip'!T3:U27,'Code Descrip'!X3:Y48),2,0),"")
    >
    > Hope this helps!
    >
    > In article <8ECDC487-CF58-4BB2-97ED-C2C5B655575D@microsoft.com>,
    > lpj <lpj@discussions.microsoft.com> wrote:
    >
    > > I currently have my Vlookup stmnt as this:
    > >
    > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > >
    > > 'Code Decrip' is the name of the worksheet
    > > I need to add another range X$3:Y48
    > > What is the proper syntax - I wasnt able to get it right after searching
    > > online for it.
    > > Thanks so much.

    >


  11. #11
    L. Howard Kittle
    Guest

    Re: multiple ranges on Vlookup

    Hi Kassis,

    I this a typo on the first line?

    >=IF(E2="","",IF(ISERROR(VLOOKUP('Code
    >Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
    >Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))


    Should be

    =IF(E2="","",IF(ISERROR(VLOOKUP(E2,'Code
    etc...

    Regards,
    Howard

    "lpj" <lpj@discussions.microsoft.com> wrote in message
    news:8ECDC487-CF58-4BB2-97ED-C2C5B655575D@microsoft.com...
    >I currently have my Vlookup stmnt as this:
    >
    > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    >
    > 'Code Decrip' is the name of the worksheet
    > I need to add another range X$3:Y48
    > What is the proper syntax - I wasnt able to get it right after searching
    > online for it.
    > Thanks so much.




  12. #12
    lpj
    Guest

    Re: multiple ranges on Vlookup

    Thanks so much - to all of you! I really appreciate it!

    "L. Howard Kittle" wrote:

    > Hi Kassis,
    >
    > I this a typo on the first line?
    >
    > >=IF(E2="","",IF(ISERROR(VLOOKUP('Code
    > >Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
    > >Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))

    >
    > Should be
    >
    > =IF(E2="","",IF(ISERROR(VLOOKUP(E2,'Code
    > etc...
    >
    > Regards,
    > Howard
    >
    > "lpj" <lpj@discussions.microsoft.com> wrote in message
    > news:8ECDC487-CF58-4BB2-97ED-C2C5B655575D@microsoft.com...
    > >I currently have my Vlookup stmnt as this:
    > >
    > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
    > >
    > > 'Code Decrip' is the name of the worksheet
    > > I need to add another range X$3:Y48
    > > What is the proper syntax - I wasnt able to get it right after searching
    > > online for it.
    > > Thanks so much.

    >
    >
    >


+ 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