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),""))

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[/TR]
[TR]
[TD]b100[/TD]
[TD]50[/TD]
[TD]filling[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67, width: 64"]filling[/TD]
[TD][/TD]
[TD]b100[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD]b100[/TD]
[TD]100[/TD]
[TD]filling[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD][/TD]
[TD][/TD]
[TD]b100[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD]b101[/TD]
[TD]50[/TD]
[TD]pre comp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b101[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]b101[/TD]
[TD]100[/TD]
[TD]filling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b102[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD]b102[/TD]
[TD]50[/TD]
[TD]filling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b103[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD]b102[/TD]
[TD]100[/TD]
[TD]filling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b104[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD]b103[/TD]
[TD]50[/TD]
[TD]pre comp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b105[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]b103[/TD]
[TD]100[/TD]
[TD]filling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b106[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

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