Too many Arguments--INDEX issues

[Novellas]

=VLOOKUP($A$3, 'SAP Data'!$A$3:$A$7,1,FALSE,INDEX('SAP Data'!$AL$3:$AL$4805,SMALL(IF('SAP Data'!$AK$3:$AK$4805>=H3,IF('SAP Data'!$AK$3:$AK$4805<=N3,ROW('SAP Data'!$AL$3:$AL$4805)-ROW('SAP Data'!$AL$3)+1)),ROWS(H$3:H3))))

The above is the formula I'm using, just a little long. I'm using MSExcel'10

What I'm trying to accomplish is I'm trying to poplulate a SS with info from another SS where the exact item and correponding qty are pulled IF it meets a specified timeframe. The specified timeframe is based on dates in seperate colums, in this case columns H & N of SS-1. The formula uses this set-up date parameter to search a column of dates in SS-2 (aka SAP Data, SS-2 col AK), & once it locates a qty (SS-2, col AL) that meets the timeframe constarint from SS-1, it should populate the SS-1 formula col with the data.
However, my prob with this formula is it's pulling any qty whose item name closely resembles each other, so that's why I tried to use VLOOKUP, but once I added it to my formula was told it was too long, too many arguments.

Need some help--Please!!

Also, not sure what the formula would do if there were 2 qtys for the same exact item name--they can't both pop the formula cell, unless there's a way it can be written in the formula that the 2+ qtys can be added together---any ideas on how to accomplish that?

thank you for your expertise

 

[Robert Mika]

VLOOKUP does not have 5th argument-this is what you are trying to do.
Can you post your data with desired result?
You can download and install two of the following programs:
HTLMaker
or
Excel Jeanie

or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[Novellas]

[TABLE="width: 637"]
<COLGROUP><COL style="WIDTH: 219pt; mso-width-source: userset; mso-width-alt: 10678" width=292><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 183pt; mso-width-source: userset; mso-width-alt: 8923" width=244><TBODY>[TR]
[TD="width: 292, bgcolor: transparent"]SS1 a[/TD]
[TD="class: xl66, width: 85, bgcolor: transparent"]H[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: transparent"]N[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 244, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Customer[/TD]
[TD="class: xl69, bgcolor: transparent"]Last Inv Date[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]Recent Inv[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Deliveries[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]ANDERSON 10-19 1-24H AMethanol[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6/1/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/22/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: #92d050"]Want "Invoice Qty" to pop here[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]ANDERSON-CURTIS 10-19 1-24H13 AMethanol[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6/13/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/22/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl81, width: 244, bgcolor: transparent"]What can be done if 2+ data fall in same time duration?[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]ANDERSON-CURTIS 10-19 4-24H13 AMethanol[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6/25/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/22/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]BROWN 9-17 3-17H AMethanol[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/7/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/22/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]BURRIS 9-18 1-23H AMethanol[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/22/2012[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]BURRIS 9-18 1-23H AMethanol[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]7/20/2012[/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]7/22/2012[/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SS-2 "SAP Data" A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AK[/TD]
[TD="bgcolor: transparent"]AL[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Customer[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: yellow"]Delivery Date[/TD]
[TD="class: xl78, bgcolor: #9bbb59"]Invoice Qty[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]ANDERSON 10-19 1-24H AMethanol[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6/15/2012[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]50.000[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]ANDERSON-CURTIS 10-19 1-24H13 AMethanol[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6/22/2012[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]50.0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]ANDERSON-CURTIS 10-19 4-24H13 AMethanol[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6/28/2012[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]50.0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]BROWN 9-17 3-17H AMethanol[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/8/2012[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]50.0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]BURRIS 9-18 1-23H AMethanol[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7/20/2012[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]50.0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]BURRIS 9-18 1-23H AMethanol[/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]7/21/2012[/TD]
[TD="class: xl76, bgcolor: transparent"] 50.0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Novellas]

Hope this helps....apologize for the delay.

 

[Novellas]

Need some direction on INDEX, VLOOKUP

=VLOOKUP($A$3,$A$15:$A$20,1,FALSE,($D$15:$D$20,SMALL(IF($C$15:$C$20>=B4,IF($C$15:$C$20<=D4,ROW($D$15:$D$20)-ROW($D$15:$D21)+1)),ROWS($B$3:B4)))


[TABLE="width: 820"]
<TBODY>[TR]
[TD]Customer</SPAN>
[/TD]
[TD]Last Inv Date</SPAN>
[/TD]
[TD][/TD]
[TD]Recent Inv</SPAN>
[/TD]
[TD][/TD]
[TD]Deliveries/Invoice Qty</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ANDERSON 10</SPAN>
[/TD]
[TD]6/1/2012</SPAN>
[/TD]
[TD][/TD]
[TD]7/22/2012</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Want "Invoice Qty" to pop here <-----</SPAN>
[/TD]
[/TR]
[TR]
[TD]ANDERSON-CURTIS 10</SPAN>
[/TD]
[TD]6/13/2012</SPAN>
[/TD]
[TD][/TD]
[TD]7/22/2012</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]What can be done if 2+ data fall in same time duration?</SPAN>
[/TD]
[/TR]
[TR]
[TD]ANDERSON-CURTIS 10</SPAN>
[/TD]
[TD]6/25/2012</SPAN>
[/TD]
[TD][/TD]
[TD]7/22/2012</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BROWN </SPAN>
[/TD]
[TD]7/7/2012</SPAN>
[/TD]
[TD][/TD]
[TD]7/22/2012</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BURRIS 9</SPAN>
[/TD]
[TD]7/19/2012</SPAN>
[/TD]
[TD][/TD]
[TD]7/22/2012</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BURRIS 9</SPAN>
[/TD]
[TD]7/20/2012</SPAN>
[/TD]
[TD][/TD]
[TD]7/22/2012</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer</SPAN>
[/TD]
[TD][/TD]
[TD]Delivery Date</SPAN>
[/TD]
[TD]Invoice Qty</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ANDERSON 10</SPAN>
[/TD]
[TD][/TD]
[TD]6/15/2012</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ANDERSON-CURTIS 10</SPAN>
[/TD]
[TD][/TD]
[TD]6/22/2012</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ANDERSON-CURTIS 10</SPAN>
[/TD]
[TD][/TD]
[TD]6/28/2012</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BROWN </SPAN>
[/TD]
[TD][/TD]
[TD]7/8/2012</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BURRIS 9</SPAN>
[/TD]
[TD][/TD]
[TD]7/20/2012</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BURRIS 9</SPAN>
[/TD]
[TD][/TD]
[TD]7/21/2012</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]






The above is the formula I'm using (just a little long) and simplified Ex of data sheet (it's much larger). I'm using MSExcel'10

What I'm trying to accomplish is I'm trying to poplulate a SS with info from another SS where the exact item and correponding qty are pulled IF it meets a specified timeframe. The specified timeframe is based on dates in seperate colums, in this case columns B & D. The formula uses this set-up date parameter to search a column of dates (Delivery Date col C), & once it locates a qty (Invoice Qty, col D) that meets the timeframe constraints (B&D), it should populate the Deliveries/Invoice Qty formula col with the data.
However, my prob with this formula is it's pulling any qty whose item name closely resembles each other, so that's why I tried to use VLOOKUP, but once I added it to my formula was told it was too long, too many arguments.

Need some help--Please!!

Also, not sure what the formula would do if there were 2 qtys for the same exact item name--they can't both pop the formula cell, unless there's a way it can be written in the formula that the 2+ qtys can be added together---any ideas on how to accomplish that?

thank you for your expertise

 

[gsistek]

Re: Need some direction on INDEX, VLOOKUP

Try using:
=SUMIFS($D$15:$D$20,$A$15:$A$20,$A3,$C$15:$C$20,">="&$B3,$C$15:$C$20,"<="&$D3)

 

[Novellas]

Re: Need some direction on INDEX, VLOOKUP

Works great!...Only one question, this formula will only pull that data that "Exactly" matches the Customer name, even in light of similar Customer name variants?

 

[Joe4]

Re: Need some direction on INDEX, VLOOKUP

I have merged your two threads together. I nt he future, please do not start a new thread on the same topic. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: </SPAN>Forum Rules).</SPAN>

 

[Novellas]

Re: Need some direction on INDEX, VLOOKUP

Apologies all around--my bad.

 

[gsistek]

Re: Need some direction on INDEX, VLOOKUP

Works great!...Only one question, this formula will only pull that data that "Exactly" matches the Customer name, even in light of similar Customer name variants?

Correct. It has to be an EXACT match.