Alternate formula required

tikku

New Member
Joined
Dec 3, 2010
Messages
48
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

BCDEFG
1SR noTypeQTYBooking dateDelivery dateResult
2#A110QWE200.00-27-Jan-14200.00
3#A111QWE300.00-27-Jan-14
4#A112QWE400.0024-Jan-1427-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
CellFormula
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
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
Joined
Dec 3, 2010
Messages
48
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

Active Member
Joined
Aug 4, 2013
Messages
255
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,601
Messages
5,625,749
Members
416,133
Latest member
ToseSenpai

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top