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

	A	B	C	D
1	MODEL	START DATE	END DATE	REWARD
2	1234	6/1/2018	7/1/2018	$61
3	1234	7/3/2018	7/7/2018	$73
4	A5414	6/1/2018	8/1/2018	$6

<tbody>
</tbody>

Then I have my information

	L	M	N	O	P
1	MODEL	PO#	BRAND	DATE ORDERED	REWARD
2	1234	A91	LB	6/27/2018	$61
3	1234	A92	LB	6/29/2018	$61
4	1234	A92	LB	6/29/2018	$0
5	A5414	A93	TR	6/1/2018

<tbody>
</tbody>

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)