Alternate formula required

[tikku]

Hi Everybody,

I would require an alternate formula for the below.

Here i mean alternate formula is i want to use vlookup, AND and If condition. i don’t want to use a ARRAY formula.

The brief explanation of how the result is generated.

1. The result should satisfy 3 conditions
a. The QTY should satisfy “Sr no” “Type” and “Delivery date” then throws the result.
For example to get QTY 200 of SR No #a110, Type “QWE” and delivery date should be satisfied.
2. For #A112 the result should satisfy 4 conditions
a. The QTY should satisfy “Sr no” “Type” “Delivery date” and “Booking date” then throws the result.
For example to get QTY 200 of SR No #a110, Type “QWE”, Delivery date and Booking Date should be satisfied.

Sheet1

	B	C	D	E	F	G
1	SR no	Type	QTY	Booking date	Delivery date	Result
2	#A110	QWE	200.00	-	27-Jan-14	200.00
3	#A111	QWE	300.00	-	27-Jan-14
4	#A112	QWE	400.00	24-Jan-14	27-Jan-14

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 82px;"><col style="width: 88px;"><col style="width: 90px;"><col style="width: 147px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =TODAY() G2 {=+INDEX(D2:D4,MATCH(1,(B2:B4="#A110")*(C2:C4="QWE"),0))} F3 =TODAY() E4 =WORKDAY(TODAY()-3,0) F4 =TODAY() <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[exceldevs]

Hi tikku,

May be this?
=INDEX($D$1:$D$4,SUMPRODUCT(($B$2:$B$4="#A110")*($C$2:$C$4="QWE")*ROW($B$2:$B$4)))

 

[tikku]

Thanks for the reply.

Could you please provide solution to second condition.
For #A112 the result should satisfy 4 conditions
a. The QTY should satisfy “Sr no” “Type” “Delivery date” and “Booking date” then throws the result.
For example to get QTY 200 of SR No #a110, Type “QWE”, Delivery date and Booking Date should be satisfied.

 

[exceldevs]

Try this for second condition, you'll get #VALUE! if there's no match (use IFERROR if you want to replace #VALUE! to something else)
=INDEX($D$2:$D$4,SUMPRODUCT(($B$2:$B$4="#A112")*($C$2:$C$4="QWE")*($E$2:$E$4=DATE(2014,1,25))*($F$2:$F$4=DATE(2014,1,27))*ROW($B$2:$B$4))-1)

Amendment to first condition (earlier formula will return unintended result if no match)
=INDEX($D$2:$D$4,SUMPRODUCT(($B$2:$B$4="#A110")*($C$2:$C$4="QWE")*ROW($B$2:$B$4))-1)