I'm missing something with my IFERROR(LOOKUP function

[miken54]

I am having an issue and was hoping someone can help me. I'm new to excel so I basically just searched old threads to come up with this formula; but ts not working 100%. Maybe this is just wrong for my needs but it seems to be working in some cases, just not all.

=IFERROR(LOOKUP(1E+307,1/((L2=A:A)*(O2>=B:B)*(I2<=C:C))*D:D),0)

I am trying to view all of my open POs with a certain vendor. We get "rewards" of different dollar amounts based off the model sold and when we ordered that model. So if I order for example model 1234 on 7/5/2018, I want to get the corresponding reward based off what programs are offered today. So in columns A-D my sheet looks like

Ex of what programs the vendor offers
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MODEL[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]REWARD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]6/1/2018[/TD]
[TD]7/1/2018[/TD]
[TD]$61[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1234[/TD]
[TD]7/3/2018[/TD]
[TD]7/7/2018[/TD]
[TD]$73[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A5414[/TD]
[TD]6/1/2018[/TD]
[TD]8/1/2018[/TD]
[TD]$6[/TD]
[/TR]
</tbody>[/TABLE]

Then I have my information
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MODEL[/TD]
[TD]PO#[/TD]
[TD]BRAND[/TD]
[TD]DATE ORDERED[/TD]
[TD]REWARD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]A91[/TD]
[TD]LB[/TD]
[TD]6/27/2018[/TD]
[TD]$61[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1234[/TD]
[TD]A92[/TD]
[TD]LB[/TD]
[TD]6/29/2018[/TD]
[TD]$61[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1234[/TD]
[TD]A92[/TD]
[TD]LB[/TD]
[TD]6/29/2018[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A5414[/TD]
[TD]A93[/TD]
[TD]TR[/TD]
[TD]6/1/2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In P2 (and all the way down), I entered =IFERROR(LOOKUP(1E+307,1/((L2=A:A)*(O2>=B:B)*(I2<=C:C))*D:D),0)

Whats odd is that its working for a few lines perfectly, returning the exact result that I am looking for. However if you look at rows 3 and 4, the same model ordered on the second date returns one reward but then in the next, it comes up a "$0".

I cant figure out what I am doing wrong. Any help would be appreciated.

 

[steve the fish]

Try:

=IFERROR(LOOKUP(2,1/(($A$2:$A$4=L2)*($B$2:$B$4 < =O2)*($C$2:$C$4>=O2)),$D$2:$D$4),0)

 

[miken54]

Thank you so much! Worked great!

Try:

=IFERROR(LOOKUP(2,1/(($A$2:$A$4=L2)*($B$2:$B$4 < =O2)*($C$2:$C$4>=O2)),$D$2:$D$4),0)