Results 1 to 6 of 6

Thread: Lookup (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup (2000)

    Edited by HansV to present data in table format

    Hello I have a table on one sheet, I would like to return on a row in another a few cell values from a row in the table. The 'chosen' row will be driven by two cell values in that row...in context:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td>XX</td><td>YY</td><td align=right>30</td><td align=right>40</td><td align=center>2</td><td>AA</td><td>BB</td><td align=right>30</td><td align=right>10</td><td align=center>3</td><td>EE</td><td>FF</td><td align=right>40</td><td align=right>10</td><td align=center>4</td><td>ZZ</td><td>LL</td><td align=right>40</td><td align=right>9</td></table>I need to return a row for values in columns A and B to my other sheet for the lowest cell value in D provided the value in C for that row is 30. I.e from this table I need AA in A2 and BB B2 to be displayed on my other sheet since 10 is the lowest from D where the C value is 30.
    Hope this makes sense, many thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (2000)

    I'd use some intermediate formulas. See attached demo.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (2000)

    1) The intermediate formulas are array formulas, i.e. you have to confirm them with Ctrl+Shift+Enter. You can see that they are array formulas in my sample workbook because there are braces { } around the formula. (Note: you shouldn't type those braces yourself, you must confirm the formula with Ctrl+Shift+Enter to make it into an array formula)

    2) Your formulas are inconsistent.
    In cell E31 on Sheet1, you refer to L10:L1010 and M10:M1010, but then in E32 you refer to H31:H354 and I31:I35
    Also, you use E30&"|"&31 instead of E30&"|"&E31

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    thanks Hans, now resolved. First time I have used arrays so a valuable lesson has been had.

    Kind regards Darren.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    Observe that I added another record to the sample you provided.

    Sheet2...

    B1: 30

    B2: 1

    B3:

    Control+shift+enter...

    =SUM(IF(Sheet1!C2:C6=B1,IF(Sheet1!D26=MIN(IF(Sheet1!C2:C6=B1,Sheet1!D26)),1)))

    A5:

    Control+shift+enter...

    =IF(ROWS($A$5:A5)<=$B$3,SMALL(IF(Sheet1!$C$2:$C$6= $B$1,IF(Sheet1!$D$2:$D$6=MIN(IF(Sheet1!$C$2:$C$6=$ B$1,Sheet1!$D$2:$D$6)),ROW(Sheet1!$D$2:$D$6)-ROW(Sheet1!$D$2)+1)),ROWS($A$5:A5)),"")

    and copy down.

    B5, copy across then down:

    =IF(N($A5),INDEX(Sheet1!A$2:A$6,$A5),"")
    Attached Files Attached Files
    Microsoft MVP - Excel

Posting Permissions

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