+ Reply to Thread
Results 1 to 6 of 6

Searching multiple columns and returning data from single, smaller column

  1. #1
    Registered User
    Join Date
    10-28-2009
    Location
    Wilmington,DE
    MS-Off Ver
    Excel 2007
    Posts
    39

    Searching multiple columns and returning data from single, smaller column

    Hello all,

    I have been trying to solve this problem for a while with no results. In my search here, I did not find exactly what I was looking for but admit that I may have missed it.

    Attached is a sample spreadsheet with data set up in a similar fasion to what my spreadsheet looks like. The data begins in 2007 and goes through April, 2011. I separate out the data by month from a dedicated data spreadsheet. Columns A:G are data entries. H and I are calculations. Columns R and S mimic D and F with the exception that they are neither day not year-specific - that is, whatever value is in HE 1 for AECo is the same value for every day in April and for each year of data.

    Columns K and H are two of my attempts - not pretty.

    Ideally, I would like to compare D2 and G2 with R2:S7 to return -1, which will then be used to calculate a daily profit from either "net profit long" or "net Profit short."

    Thanks,

    Chris
    Attached Files Attached Files
    Last edited by NBVC; 05-06-2011 at 10:14 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Searching multiple columns and returning data from single, smaller column

    I think you almost had it in column L... should be:

    =LOOKUP(2,1/(D2=$R$2:$R$7)/(G2=$S$2:$S$7),$T$2:$T$7)

    or

    =LOOKUP(2,1/((D2=$R$2:$R$7)*(G2=$S$2:$S$7)),$T$2:$T$7)

    you are only looking at one row's items at a time and comparing to your table in R:T
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-28-2009
    Location
    Wilmington,DE
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Searching multiple columns and returning data from single, smaller column

    Thanks, NBVC.

    That worked! I should written earlier and saved myself a lot of hours>

    Can a similar approach be used for 3 or more columns? I have to admit that my attempt in L was a last-ditch effort and that I copied it. I don't really understand the 2/1 part of the formula.

    Chris

  4. #4
    Registered User
    Join Date
    10-28-2009
    Location
    Wilmington,DE
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Searching multiple columns and returning data from single, smaller column

    OK, I just looked again at the formula and I think the 2/1 means look at 2 variables and get one. Therefore, I could use 3 or some other number greater than 2.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Searching multiple columns and returning data from single, smaller column

    The LOOKUP(2,1/()) construct is simply looking for a 2 in an array of 1's and #Div/0! errors gotten from the conditions you checked.

    Lookup looks for the last value in an array that is smaller than or equal to the lookup value. In this case 2 is the lookup value and the results of your conditional checks will result in an array of 1's (where matches occur) and #DIV/0! (where matches don't occur).

    Lookup ignored the errors and therefore the last (and in your case only) 1 is what is returned as the matched item, and then the appropriate value is taken from T.

    So if you have want to return values from another column in a bigger table, just refer to that column like you did with T2:T7.

    If you are adding another condition, use the 2nd version that I showed above and add it as another *(condition) within the main brackets ().

  6. #6
    Registered User
    Join Date
    10-28-2009
    Location
    Wilmington,DE
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Searching multiple columns and returning data from single, smaller column

    Thank you - I will look a little more closely at the formulas and your description.

    Chris

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1