Multiple conditions in an 'IF' statement

[dlburkins]

I have a chart that has four columns. I want to bring back the corresponding percentage based upon a few different criteria.

For example: In cell C2, I am trying to attempt the following
- Use the corresponding title in column 'H' that matches the title in column 'A' and then also find the appropriate revenue range in columns 'A&B' and return the appropriate percentage 'column 'K' to column 'C'

For the attached document I have included three samples of what I am trying to do. You will notice what I am trying to automate is in column 'C'.

I am able to do this if I break out each title by separate tables, but I would like to use one data table for the formula if at all possible.

Any help will be appreciated.

[NBVC]

Try:

=LOOKUP(2,1/(($H$2:$H$19=A2)*($I$2:$I$19<=B2)),$K$2:$K$19)

copied down.

[dlburkins]

Thanks, this is just what I needed Could you breakdown the formula for me..I hate copying and pasting things that I dont fully understand.

[NBVC]

This formula looks for the last time a condition is found to be true.

Lookup() finds the last time that a value is smaller than or equal to a lookup value.

The 2 conditions: ($H$2:$H$19=A2)*($I$2:$I$19<=B2) create two ranges of TRUEs/FALSEs and multiply them together to produce a single array of 1's and 0's (the 1's occur when corresponding conditions are both TRUE, all other combinations produce 0 after multiplication). The 1/(($H$2:$H$19=A2)*($I$2:$I$19<=B2)) then produces an array of 1's and #DIV/0! errors because you are dividing 1 by either 1 or 0.

Now we are trying to find a 2 in that array of results via the LOOKUP function, and since you have either 1 or $DIV/0 errors only, it returns the last 1 (which is the last entry that is smaller than or equal to the lookup value of 2)... the third argument of the LOOKUP function allows the function to return the corresponding item in that range to where the last 1 occurred.