Vlookup and Multiple Ranges

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Vlookup and Multiple Ranges

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Location
    Netherlands
    Posts
    592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup and Multiple Ranges

    Hi all

    Does any of you know of a formula for column "K" that finds the corresponding amount from the ranges A:b, D:E or G:H?



    Sheet1
    ABCDEFGHIJK
    1test1test2test3
    2fec -75,00 vgrythggw -135,00 fec -75,00
    3nhyn -41,00 gty -400,00 hjytrjrnhyn -41,00
    4cfgbth -12,34 hytgrf -150,00 yhjicfgbth -12,34
    5hytryjnhyt -54,99 frtghytryjnhyt -54,99
    6rvf -89,00 rdetgrvf -89,00
    7rw -559,07 huygtrw -559,07
    8thy -30,00 ui7juthy -30,00
    9btrer -12,83 btrer -12,83
    10bnnj -29,50 bnnj -29,50
    11uygvrw -56,00 uygvrw -56,00
    12vgr
    13gty -400,00
    14hytgrf -150,00
    15frtg
    16rdetg
    17huygt
    18ui7ju
    19ythggw -135,00
    20hjytrjr
    21yhji



  2. #2
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,314
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup and Multiple Ranges

    If there is no risk of duplicate entries in the A:H range, and you don't have a massive amount of data to work with

    =INDIRECT(ADDRESS(SUMPRODUCT(($A$2:$H$11=J3)*ROW($A$2:$H$11)),SUMPRODUCT(($A$2:$H$11=J3)*COLUMN($A$2:$H$11))+1))

    Otherwise it would be something on the lines of

    =IF(ISNA(VLOOKUP(J2,$A$2:$B$11,2,0)),IF(ISNA(VLOOKUP(J2,$D$2:$E$11,2,0)),IF(ISNA(VLOOKUP(J2,$G$2:$H$11,2,0)),0,VLOOKUP( J2,$G$2:$H$11,2,0)),VLOOKUP(J2,$D$2:$E$11,2,0)),VLOOKUP(J2,$A$2:$B$11,2,0))

    Or (excel 07 or later)

    =IFERROR(VLOOKUP(J2,$A$2:$B$11,2,0),IFERROR(VLOOKUP(J2,$D$2:$E$11,2,0),IFERROR(VLOOKUP(J2,$G$2:$H$11,2,0),0)))
    Last edited by jasonb75; Aug 2nd, 2010 at 05:51 PM.

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Location
    Netherlands
    Posts
    592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup and Multiple Ranges

    Thanks Joson,
    This helped me a lot!

  4. #4
    Board Regular
    Join Date
    Feb 2008
    Posts
    404
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup and Multiple Ranges

    Hi macah!t

    Try this. For simplicity, assume your ranges are a2:b4,d2:e4,and g2:h4 respectively. Assume your results range begins at j2:k2 (goes downward). J2 is the lookup value, K2 is your where your formula to be copied down

    in K2 =lookup(99^99,choose({1,2,3},vlookup(j2,a2:b4,2,false),vlookup(j2,d2:e4,2,false),vlookup(j2,g2:h4,2,false))). If you are solving for text entries, use =rept("z",255)..... instead of 99^99. Use $ to lock your ranges to absolute values.Copy down

    Or in K2 =lookup(99^99,choose({1,2,3},index(b2:b4,match(j2,a2:a4,0)),index(e2:e4,match(j2,d2:d4,0)),index(h2:h4,match(j2,g2:g4,0) ))). Lock in your index ranges and lookup ranges. Copy down
    HTH,
    Mike Szczesny

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,452
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup and Multiple Ranges

    Try...

    K2, just enter and copy down:

    =LOOKUP(9.99999999999999E+307,
    CHOOSE({1,2,3,4},
    0,
    VLOOKUP(J2,$A$2:$B$11,2,0),
    VLOOKUP(J2,$D$2:$E$8,2,0),
    VLOOKUP(J2,$G$2:$H$4,2,0)))
    [/code]

    Note 1. On European systems, replace the decimal dot in the big number with comma and use semi-colon between terms instead of comma.

    Note 2. Custom format the result range in column K as [=0]"";General.
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com