Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Vlookup Based On Two Conditions

  1. #1
    Join Date
    12th January 2007
    Posts
    17

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Davidoff; March 8th, 2007 at 00:20.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st December 2006
    Posts
    1,047

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; September 8th, 2008 at 14:37.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th January 2007
    Posts
    17

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th January 2006
    Posts
    524

    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 at 21:54.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th January 2007
    Posts
    17

    Re: Vlookup Based On Two Conditions (columns)

    Works great with the INDEX function. Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    11th January 2011
    Posts
    1

    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 at 01:21. Reason: delete quote

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,102

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Vlookup With 2 Cells As Conditions
    By spinster in forum EXCEL HELP
    Replies: 4
    Last Post: February 11th, 2012, 00:03
  2. Vlookup With 2 Conditions
    By CustomGT in forum EXCEL HELP
    Replies: 9
    Last Post: June 28th, 2007, 01:24
  3. Vlookup Based On Results From A Vlookup
    By pzarate in forum EXCEL HELP
    Replies: 1
    Last Post: June 9th, 2007, 03:49
  4. IF, Vlookup, Indirect & Sum With Conditions
    By Timbo in forum EXCEL HELP
    Replies: 11
    Last Post: November 9th, 2006, 01:02
  5. vlookup with conditions
    By lorkmc in forum EXCEL HELP
    Replies: 6
    Last Post: June 9th, 2005, 00:06

Bookmarks

Posting Permissions

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