Too many Arguments--INDEX issues

Novellas

New Member
Joined
Aug 15, 2012
Messages
41
=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!!:eek:

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
SS1 aHN
CustomerLast Inv Date Recent Inv Deliveries
ANDERSON 10-19 1-24H AMethanol6/1/2012 7/22/2012 Want "Invoice Qty" to pop here
ANDERSON-CURTIS 10-19 1-24H13 AMethanol6/13/2012 7/22/2012 What can be done if 2+ data fall in same time duration?
ANDERSON-CURTIS 10-19 4-24H13 AMethanol6/25/2012 7/22/2012
BROWN 9-17 3-17H AMethanol7/7/2012 7/22/2012
BURRIS 9-18 1-23H AMethanol7/19/2012 7/22/2012
BURRIS 9-18 1-23H AMethanol7/20/2012 7/22/2012
SS-2 "SAP Data" AAKAL
Customer Delivery DateInvoice Qty
ANDERSON 10-19 1-24H AMethanol 6/15/201250.000
ANDERSON-CURTIS 10-19 1-24H13 AMethanol 6/22/201250.0
ANDERSON-CURTIS 10-19 4-24H13 AMethanol 6/28/201250.0
BROWN 9-17 3-17H AMethanol 7/8/201250.0
BURRIS 9-18 1-23H AMethanol 7/20/201250.0
BURRIS 9-18 1-23H AMethanol 7/21/2012 50.0

<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>
</TBODY>
 
Upvote 0
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)))


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

<TBODY>
</TBODY>






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!!:eek:

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
 
Last edited:
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top