Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: lookup across two columns then get the corresponding data.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    The lookup functions on excel do not satisfy my requirements.

    I have two columns then data, like this:

    Class Type NPols
    ANN 507 100
    ANN 508 123
    ANN 552 6903
    ANN 548 1234
    ANW 507 4520
    ANW 508 23520
    ANW 510 2420
    *** 507 5285
    *** 509 2520
    *** 510 2343

    Now, I want to retrieve the data NPOLS for "*** 507" say so the function would pick out 5285.

    Any help on this appreciated. I don't want a VBA solution sorry I want to be able to do this on the worksheet if possible.

    A related problem was the following, I had data like this

    Year Policy Data1 Data2
    1991 Life 2345 42420
    Pension 2355 2449
    1992 Life 94802 42420
    Pension 4295 24249
    1993 Life 2420 63729
    Pension 24020 74240

    etc.

    and I just couldn't come up with a lookup solution to find Data2 for 1992 Pension say. The required function would give me 24249.

    Again, I just can't change the labels and I require a lookup solution, not VBA please.


    Many thanks,

    RET79

    [ This Message was edited by: RET79 on 2002-05-09 12:18 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your data is in A1:C11 use...

    {=INDEX(C2:C11,MATCH("ASS507",A2:A11&B2:B11,0))}

    ...Or, if you're really determined to include the intervening space character...

    {=INDEX(C2:C11,MATCH("*** 507",A2:A11&" "&B2:B11,0))}

    Note: These are an array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    [ This Message was edited by: Mark W. on 2002-05-09 12:27 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 12:16, RET79 wrote:
    Hi.

    The lookup functions on excel do not satisfy my requirements.

    I have two columns then data, like this:

    Class Type NPols
    ANN 507 100
    ANN 508 123
    ANN 552 6903
    ANN 548 1234
    ANW 507 4520
    ANW 508 23520
    ANW 510 2420
    *** 507 5285
    *** 509 2520
    *** 510 2343

    Now, I want to retrieve the data NPOLS for "*** 507" say so the function would pick out 5285.

    Any help on this appreciated. I don't want a VBA solution sorry I want to be able to do this on the worksheet if possible.

    A related problem was the following, I had data like this

    Year Policy Data1 Data2
    1991 Life 2345 42420
    Pension 2355 2449
    1992 Life 94802 42420
    Pension 4295 24249
    1993 Life 2420 63729
    Pension 24020 74240

    etc.

    and I just couldn't come up with a lookup solution to find Data2 for 1992 Pension say. The required function would give me 24249.

    Again, I just can't change the labels and I require a lookup solution, not VBA please.


    Many thanks,

    RET79

    [ This Message was edited by: RET79 on 2002-05-09 12:18 ]
    Insert a new column before Class which combines the Class & Type columns by doing =B1&C1 in column D. Now use the new column in your vlookup formula:

    =Vlookup("ASS507",table,4,false)
    It's never too late to learn something new.

    Ricky

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Use INDEX and MATCH with a two-way lookup on the MATCH column.

    Your data table is in A1:C9

    In E1: "***" (might want to change this!)
    In E2: 507

    Array enter (!!) the following in your result cell
    =INDEX(A1:C9,MATCH(E1&E2,A1:A9&B1:B9,0),3)

    This returned 5285 for me.

    Bye,
    Jay

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 12:24, Mark W. wrote:
    If your data is in A1:C11 use...

    {=INDEX(C2:C11,MATCH("ASS507",A2:A11&B2:B11,0))}

    ...Or, if you're really determined to include the intervening space character...

    {=INDEX(C2:C11,MATCH("*** 507",A2:A11&" "&B2:B11,0))}

    ---snip---
    Nice one! I use this multi-way lookup frequently, but never like this. Cool.

  6. #6
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 12:16, RET79 wrote:
    Hi.

    The lookup functions on excel do not satisfy my requirements.

    I have two columns then data, like this:

    Class Type NPols
    ANN 507 100
    ANN 508 123
    ANN 552 6903
    ANN 548 1234
    ANW 507 4520
    ANW 508 23520
    ANW 510 2420
    *** 507 5285
    *** 509 2520
    *** 510 2343

    Now, I want to retrieve the data NPOLS for "*** 507" say so the function would pick out 5285.

    Any help on this appreciated. I don't want a VBA solution sorry I want to be able to do this on the worksheet if possible.

    A related problem was the following, I had data like this

    Year Policy Data1 Data2
    1991 Life 2345 42420
    Pension 2355 2449
    1992 Life 94802 42420
    Pension 4295 24249
    1993 Life 2420 63729
    Pension 24020 74240

    etc.

    and I just couldn't come up with a lookup solution to find Data2 for 1992 Pension say. The required function would give me 24249.

    Again, I just can't change the labels and I require a lookup solution, not VBA please.


    Many thanks,

    RET79

    [ This Message was edited by: RET79 on 2002-05-09 12:18 ]
    Ignore the 1st part I misread the question and others have posted good alternatives.

    Your 1st part of the question can be answered with,

    Search Criteria is in G1 (ie *** 507)
    Table is in A1:C11
    3 is the 3rd column
    False only picks up exact match

    =vlookup(G1,A1:C11,3,False)

    The 2nd part of your question looks like you need to reformat your table slightly and is then another VLookup.

    Code:
         A           B    C       D    E
    1 YrPol        Year Policy  Data1 Data2
    2 =B2&C2      1991 Life    2345  42420
    3 =B3&C3      1991 Pension 2355  2449
    4 =B4&C4      1992 Life    94802 42420
    5 =B5&C5      1992 Pension 4295  24249
    6 =B6&C6      1993 Life    2420  63729
    7 =B7&C7      1993 Pension 24020 74240
    I think this is the easiest way to get a table that you can use.

    Next to retrieve your values Place your query year in G1
    Place your Query Type (Pension) in G2
    in G3 place you formula
    =vlookup(G1&G2,A1:E7,5,False)

    I tested and this works.


    _________________


    [ This Message was edited by: s-o-s on 2002-05-09 13:34 ]

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Since you don't seem to have duplicate records for class and type (a prerequisite), you can also use:

    =OFFSET(C2,SUMPRODUCT((A2:A11=E1)*(B2:B11=F1),ROW(C2:C11))-2,0,1,1)

    where E1 houses a class value and F1 a type value.

    Aladin

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You guys never fail to amaze me with what you can get done on excel, WOW.

    Thanks ever so much,

    RET79

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Gosh this can even be extended to 3 way lookups too, amazing stuff.

    RET79

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    A related problem was the following, I had data like this

    Year Policy Data1 Data2
    1991 Life 2345 42420
    Pension 2355 2449
    1992 Life 94802 42420
    Pension 4295 24249
    1993 Life 2420 63729
    Pension 24020 74240

    etc.

    and I just couldn't come up with a lookup solution to find Data2 for 1992 Pension say. The required function would give me 24249.

    Again, I just can't change the labels and I require a lookup solution, not VBA please.


    This concerns thus your 2nd retrieval problem.

    I'll assume that A1:D7 houses your sample data which I reproduce here:

    {"Year","Policy","Data1","Data2";
    1991,"Life",2345,42420;
    "","Pension",2355,2449;
    1992,"Life",94802,42420;
    "","Pension",4295,24249;
    1993,"Life",2420,63729;
    "","Pension",24020,74240}

    where "" stands for blank.

    For a retrieval from "Data2" column, use;

    =OFFSET(D2,(MATCH(F1,A:A,0)+IF(G1="Pension",1,0))-2,0,1,1)

    where F1 houses a year and G1 a policy.

    Aladin

Some videos you may like

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
  •