Results 1 to 5 of 5

Vlookup and Multiple Ranges

This is a discussion on Vlookup and Multiple Ranges within the Excel Questions forums, part of the Question Forums category; Hi all Does any of you know of a formula for column "K" that finds the corresponding amount from the ...

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Location
    Netherlands
    Posts
    592

    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
    5,556

    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 04:51 PM.

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Location
    Netherlands
    Posts
    592

    Default Re: Vlookup and Multiple Ranges

    Thanks Joson,
    This helped me a lot!

  4. #4
    Board Regular
    Join Date
    Feb 2008
    Posts
    69

    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
    64,414

    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.

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