Return value with multiple conditions

[stepan1987]

Hi once again guys,

I have two sheets. The first sheet contains name of the security (column D sheet1), date (column AN) and quantity of inventory (column X) in that security on the given date.
On the second sheet there is a list of unique names of securities from the column D on first sheet (column C sheet 2) and a cell with a date (H3 sheet2).
I need to create a formula on the second sheet in column E that would return the inventory from the column X sheet 1 for the security in column C on sheet 2 for the date in H3 OR on the last date before the inputted in H3. There is also another thing - there might be changes in inventory within one date and I need the formula to return me the last figure for the date.

Could you help me out?

 

[stepan1987]

OK, let me change it a bit.
Which formula should I use if I need it to return a difference between two cells in the line where two conditions are met: same security and 1.00 ? VLOOKUP?

I need smth like: IF "column D" = "security" AND "column AO" = 1 RETURN difference between column M and column Q in the line where conditions are met

 

[MrKowz]

Try:

=INDEX(M1:M100,MATCH(1,IF(D1:D100="security",IF(AO1:AO100=1,1,"")),0))-INDEX(Q1:Q100,MATCH(1,IF(D1:D100="security",IF(AO1:AO100=1,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER to enter this as an array formula. You will know it is entered properly when you see {brackets} around the formula.

 

[stepan1987]

MrKowz, no unfortuntely it fails to return the line needed in the INDEX function.

 

[MrKowz]

In what way does it fail? Can you please provide some sample data?

 

[stepan1987]

I'm very sorry I got technical possibility to upload files at the moment.

Evaluating formula I see that i does match the name of the security but in the number-equals-one part it results FALSE and further in #VALUE

What might be wrong with that?

 

[MrKowz]

Did you confirm entry with CTRL+SHIFT+ENTER? The fact that the part where it is testing where column AO = 1 evaluates to FALSE, not an array, indicates that it was not entered as an array formula.

Follow these steps:

1. Place your cursor in the formula bar
2. Press CTRL+SHIFT+ENTER (not just enter)
3. If done properly, you should see {brackets} around the formula, and it should not evaluate to #VALUE!