Searching multiple columns and returning data from single, smaller column

**ccolton** · 05-04-2011, 04:01 PM

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

**NBVC** · 05-04-2011, 04:05 PM

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

**ccolton** · 05-04-2011, 04:13 PM

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

**ccolton** · 05-04-2011, 04:16 PM

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.

**NBVC** · 05-04-2011, 04:25 PM

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 ().