CODEWITHSUNDEEP
excelexcel-formula
Ale
Ale

Reputation: 665

How to VLOOKUP with various conditions?

I have two tables. I'd like to find a D column value from table2 that corresponds to a condition: column C of table2 is equal to column "C&D" from table1 and column I of table2 is "ok".

Table1

enter image description here

Table 2 enter image description here

I was thinking something about this formula:

=IF(AND(F3="si";KIT!I3="ok");VLOOKUP(C3&D3;KIT!C:D;2;0);)

but unfortunately it does not work and takes just the first lookup value.

Upvotes: 1

Views: 113

Answers (1)

Rory
Rory

Reputation: 34035

You can use:

=IF(F3="si";LOOKUP(2;1/(KIT!C:C=C3&D3)/(KIT!I:I="OK");KIT!D:D);0)

It would be better if you could restrict the number of rows rather than using entire column references.

To explain: (KIT!C:C=C3&D3) will return an array of True or False values. Excel will calculate those as 1 and 0 respectively. Dividing 1 by that array will return an array containing 1 or #DIV/0 (1 where the condition is met, #DIV/0 otherwise. Dividing that result by the (KIT!I:I="OK") array has a similar effect and the resulting array only contains 1 where all conditions have been met, or #DIV/0 otherwise.

Searching for 2 in this array will find the last value less than or equal to 2 in that array. Since LOOKUP ignores errors, that will be the last occurrence of 1 (the only occurrence, if only one row matches the conditions)

Finally the corresponding value from column D is returned as the result.

Hopefully that makes some sense!

Upvotes: 2

Related Questions