# Alternate formula required

#### tikku

##### New Member
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>

 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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### exceldevs

##### Active Member
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

##### New Member

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

##### Active Member
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)

Replies
4
Views
133
Replies
3
Views
347
Replies
0
Views
98
Replies
3
Views
332
Replies
1
Views
226

1,128,079
Messages
5,628,539
Members
416,324
Latest member
sam_d

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back