Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Down selecting data through multiple columns (Excel 2002)

    Would any one have an idea -- or some help..
    I have been trying to select a record from a data table. I can get the record using vlookup or index w/match if I am only looking at one column. However, I need to look at multiple columns (4 to be exact). This would be easy with a relational database but I need to use excel, hopfully w/o visual basic. Below is an example table. I will have rows of data that match Position,location,type, and year.
    I need a field to give me the year quantity for the row after it evaluates each column of the selection. (note there are duplicates in each column but there will only be one valid result after all 4 selections are evaluated.
    For instance If I have a row as follows:
    Apple Ground Out-MD 1 -------> Result cell shows 10.00
    Pear Ground Out-MD 2 -------> Result cell shows 88.00

    I have also tried nested If statements but using an array it only evaluates the first occurance.
    Any help would be appreciated

    Example table (sorry I noticed in the preview that the data doesn't stay in columns)
    Position Location type Year 1 Year 2 Year 3 Year 4
    Apple Tree In-MD 11.00 12.10 13.31 14.64
    Peach Tree In-MD 22.00 24.20 26.62 29.28
    Pear Tree In-MD 33.00 36.30 39.93 43.92
    Cherry Tree In-MD 44.00 48.40 53.24 58.56
    Plum Tree In-MD 55.00 60.50 66.55 73.21
    Apple Ground In-MD 66.00 72.60 79.86 87.85
    Peach Ground In-MD 77.00 84.70 93.17 102.49
    Pear Ground In-MD 88.00 96.80 106.48 117.13
    Cherry Ground In-MD 99.00 108.90 119.79 131.77
    Plum Ground In-MD 110.00 121.00 133.10 146.41
    Apple Ground Out-MD 10.00 11.00 12.10 13.31
    Peach Tree Out-MD 20.00 22.00 24.20 26.62
    Pear Tree Out-MD 30.00 33.00 36.30 39.93
    Cherry Tree Out-MD 40.00 44.00 48.40 53.24
    Plum Tree Out-MD 50.00 55.00 60.50 66.55
    Apple Ground Out-MD 60.00 66.00 72.60 79.86
    Peach Ground Out-MD 70.00 77.00 84.70 93.17
    Pear Ground Out-MD 80.00 88.00 96.80 106.48
    Cherry Ground Out-MD 90.00 99.00 108.90 119.79
    Plum Ground Out-MD 100.00 110.00 121.00 133.10

  2. #2
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Down selecting data through multiple columns (Excel 2002)

    Hi,

    don't know if it ill work : no vba involved several data filtering or advanced filtering ?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Down selecting data through multiple columns (Excel 2002)

    This will allow a LOOKUP of 3 (or even more columns) to get a row and then chose a fourth as as column (Year)

    Say your example table is in the range A1:G21 (row 1 has headers).
    If you put in J1:M1:
    Apple Ground Out-MD 1

    You can create an ARRAY formula (confirm with ctrl-shift-enter) eg in N1:
    <pre>=INDEX($D$2:$G$21,MATCH(J1&K1&L1,($A$2:$A$21& $B$2:$B$21&$C$2:$C$21),0),M1)</pre>

    which will result in "10"
    <pre>MATCH(J1&K1&L1,($A$2:$A$21&$B$2:$B$21&$C$2:$C $21),0)</pre>

    will find the row that the combined text in JKL matches the combined text in ABC and the whole formula looks up in the range of the 4 years, that row and the column you designate in col M

    Also for your other example (if placed in J2:M2)
    Pear Ground Out-MD 2
    and COPY the formula from N1 to N2 and you will get the result "88"

    Steve

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Down selecting data through multiple columns (Excel 2002)

    Steve

    Thanks, that is exactly what I wanted to do. I can also see a lot of other applications of this.

    Roger

Posting Permissions

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