# Searching multiple columns and returning data from single, smaller column

1. ## 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

2. ## 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

3. ## 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. ## 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. ## 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. ## 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

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

#### 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