vlookup first value based on two criteria without changing column order

[jwolf16]

Hi There,

I'm having some real trouble with what is the best way to do this:

In column J I want to return the value in column C based upon criteria in B and D.

This is the formula I have been using in J1:

=(IF(D:D = "filling",VLOOKUP(I4,B:D,2,0),""))

B	C	D	E	F	G	H	I	J
b100	50	filling			filling		b100	50
b100	100	filling					b100	50
b101	50	pre comp					b101
b101	100	filling					b102	50
b102	50	filling					b103	50
b102	100	filling					b104	50
b103	50	pre comp					b105
b103	100	filling					b106	50

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

I have tried using index/match and sumproduct but cannot quite nail it. Any help would be much appreciated.

Thanks,

 

[steve the fish]

See if this works:

=INDEX($C$1:$C$8,MATCH(1,INDEX(($D$1:$D$8=$G$1)*($B$1:$B$8=I1),0),0))

 

[jwolf16]

Wow that was quick !!

And yes, worked perfectly. Thanks so much steve the fish