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
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

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

Novellas

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

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,491
Office Version
365
Platform
Windows
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>
 

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
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.
 

Forum statistics

Threads
1,082,104
Messages
5,363,164
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top