Announcement

Collapse
No announcement yet.

Vlookup Based On Two Conditions

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Vlookup Based On Two Conditions

    Hi there

    Once again a little problem I'm not capable of solving on my own..

    I need a function to look up values vertically in a separate (other) linked Excel-file based on two conditions.

    The separate file is a rawdata-dump file and comes periodically in a fixed format. In my current file I try to go lookup certain values in that rawdata-file, based on two conditions:

    1: in A:A a month text value (MMM) in the rawdata file
    2: in B:B a one-digit year number (Y) in the rawdata file

    Because this is a serial task (I've got 18 different rawdata files) I cannot solve the problem with a simple VLOOKUP combined with CONCATENATE of A and B, I would have to manually edit each rawdata file by adding a fresh row and the CONCATENATE function..

    I found a nice formula how to solve this problem via the =INDIRECT function, but my problem is that INDIRECT only works with open linked workfiles. This means that I can only use my spreadsheet with the formula when the rawdata-file(s) is(are) opene(d), which I do not want to have open 18 times... (This formula you will find in the attachment!)

    See attachted an example of my rawdata output and how I want to lookup the values. Just imagine that the worksheet "rawdata" is a separate excel-file on a different location.

    Hope you understand...
    Greetz, D.
    Attached Files
    Last edited by Davidoff; March 8th, 2007, 01:20.

  • #2
    Re: Vlookup Based On Two Conditions (columns)

    Hi there,

    Not sure if this will suffice, but have a look at the attached where I've inserted a new tab (RawDataLookupKey) which joins columns A & B from the rawdata tab to create a lookup key for the lookup tab. This way the original remains unaltered but you'll still be able to perform a LOOKUP if the other files are open or not.

    HTH

    Robert
    : D


    ADDED BY ADMIN

    See VLOOKUP and follow the top links to x Condition VLOOKUP.
    Attached Files
    Last edited by Dave Hawley; September 8th, 2008, 14:37.

    Comment


    • #3
      Re: Vlookup Based On Two Conditions (columns)

      Hi Robert

      Thanks for your help. I know that combining the two cells to a key would be a possibility, I just tried to avoid it, since this would mean that I would replicate information of the rawdata-files in my lookup-file. This would be very much data to process..

      Luckily I found a different way in another thread of this forum I could adapt to solve the problem, which uses the INDEX function:

      =INDEX(rawdata!$C$7:$C$54;MATCH(1;(rawdata!$B$7:$B$54=C5)*(rawdata!$A$7:$A$54=B5);0))

      Needs to be entered as array-function, Ctrl-Shift-Enter, instead of only Enter. I added the function to the attachment.

      Thanks and Greets
      D.
      Attached Files

      Comment


      • #4
        Re: Vlookup Based On Two Conditions (columns)

        Hi,

        Try using sumproduct formula below:

        =SUMPRODUCT((B5=rawdata!$A$7:$A$55)*(C5=rawdata!$B$7:$B$55)*(rawdata!$C$7:$C$55))

        Sumproduct handle more then two conditions easily.

        Biz
        Last edited by ByTheCringe2; April 17th, 2007, 21:54.

        Comment


        • #5
          Re: Vlookup Based On Two Conditions (columns)

          Works great with the INDEX function. Thanks!

          Comment


          • #6
            Re: Vlookup Based On Two Conditions (columns)

            Hey Just wanted to say Thanks. It did my work..

            Thanks a Lot
            Last edited by AAE; January 12th, 2011, 02:21. Reason: delete quote

            Comment


            • #7
              Re: Vlookup Based On Two Conditions

              sakothari,

              Please do no resurrect old threads by posting in them, unless it is to provide a solution to an unresolved issue.

              Since you did post and quoted an entire post in your reply, please note the following regarding quotes:

              1. Quote ONLY if it is needed to add clarity or context for your own reply. If so, then
              2. Quote ONLY the specific part of the post that is relevant to your reply -- not the entire post.

              This will keep thread clutter to a minimum and make the discussion easier to follow.
              AAE
              ----------------------------------------------------

              Forum Rules | Message to Cross Posters | How to use Tags

              Comment

              Working...
              X