Using VLookup with IF?

[Jmpelissero]

Hello,

I have searched everywhere and cannot find how to solve this. I have a table that looks like:

A_B_C_D_E_F
1 1 1 10 123 10 ?
2 1 1 20 636
3 2 1 30 257
4 2 1 10 563
....
this goes on for about 500000 lines with many non unique values.

I want to look up the value in column D that is in the same row as the value of E is found in column C, but only if columns A and B contain the number "1". So for the same row, only if column A has a "1" and column B has a "1", do I want to find what the value of column C is where that value in column C matches the value in column E. Again, all values A to D have to be in the same row. For the above example, I would need an equation that gives me the value of "123", and not "563". I hope I explained this well enough and thank you.

 

[Andrew Poulsom]

Does this work for you?

=INDEX(D1:D4,MATCH(1,INDEX((A1:A4=1)*(B1:B4=1)*(C1:C4=E1:E4),),FALSE))

 

[Jmpelissero]

Does this work for you?

=INDEX(D1:D4,MATCH(1,INDEX((A1:A4=1)*(B1:B4=1)*(C1:C4=E1:E4),),FALSE))

Thank you so much! This worked great for one cell. I will continue to use it for my dataset and let you know if I run into any problems. If you have extra time, could you please explain to me how the equation works just so I can better understand? Thank you!

 

[Andrew Poulsom]

It uses boolean multiplication to create 1 or 0. TRUE*TRUE = 1, TRUE*FALSE or FALSE*FALSE = 0.