INDEX and MATCH formula help - can't get range to expand, and trouble with multiple criteria

[amyb2703]

At my new job, I inherited a spreadsheet that displays features on our product orders. The order specs are uploaded into a tab name DATA in the file, and the spreadsheet on the main tab looks up items on the DATA tab and then marks X in the field on the main tab when it finds a match for that particular sales code. Results look like this:

Here is an example from column I.

=IFERROR(IF(N771="X","X",INDEX(​

Data!$A$2:$N$2000​

,MATCH(1,(Data!$B$2:$B$2000=B771)*(Data!$I$2:$I$2000="TCO302"),0),14)),"")​

My first issue is around finding multiple criteria (red item). Recently our product offerings have been increased, and we now have multiple sales codes in each category. The formula today looks for a single item to match and then inserts an "X" in the field. I need it to create the "X" in the field for multiple sales codes. In the example below for column I, it only inserts "X" when TCO302 is found. I need it to insert "X" when either TCO302 or TCO332 or TCO333 is found.


My second issue is increasing the range (green item). Currently the formula only looks for items through row 2000. Our business has grown, and I have order information that is beyond that. Seems like I should just be to change the $N$2000 to $N$5000 or whatever I like, but it doesn't seem to want to work.

Thanks in advance for any help!

 

[amyb2703]

Here is a portion of the table:

[TABLE="class: grid, width: 732"]
<tbody>[TR]
[TD]Order Date[/TD]
[TD]Order #[/TD]
[TD]Build #[/TD]
[TD]Sched[/TD]
[TD]Customer[/TD]
[TD]Sales[/TD]
[TD]VIN[/TD]
[TD]Model[/TD]
[TD]302, 332, 333[/TD]
[TD]702[/TD]
[TD]902, 903[/TD]
[/TR]
[TR]
[TD="colspan: 8"]ORDER INFO[/TD]
[TD]OPTIONS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/1[/TD]
[TD]TCE40888[/TD]
[TD="align: right"]7402[/TD]
[TD]yes[/TD]
[TD]C C Distributors, Inc - Corpus Christi[/TD]
[TD]T-3[/TD]
[TD][/TD]
[TD]SLT - CUSTOM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/1[/TD]
[TD]TCE40901[/TD]
[TD="align: right"]7403[/TD]
[TD]yes[/TD]
[TD]J&R Ransom Trucking Inc. - Rose Creek[/TD]
[TD]T-8[/TD]
[TD][/TD]
[TD]BDF1200-P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/2[/TD]
[TD]TCE40923[/TD]
[TD="align: right"]7404[/TD]
[TD]yes[/TD]
[TD]STOCK[/TD]
[TD]T-99[/TD]
[TD][/TD]
[TD]CT100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/2[/TD]
[TD]TCE40924[/TD]
[TD="align: right"]7405[/TD]
[TD]yes[/TD]
[TD]STOCK[/TD]
[TD]T-99[/TD]
[TD][/TD]
[TD]CT100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/2[/TD]
[TD]TCE40925[/TD]
[TD="align: right"]7406[/TD]
[TD]yes[/TD]
[TD]STOCK[/TD]
[TD]T-99[/TD]
[TD][/TD]
[TD]CT100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/8[/TD]
[TD]TCE40972[/TD]
[TD="align: right"]7407[/TD]
[TD]yes[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD]T-5[/TD]
[TD][/TD]
[TD]TCO690M-G3[/TD]
[TD]x[/TD]
[TD]X[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD="align: right"]8/8[/TD]
[TD]TCE40973[/TD]
[TD="align: right"]7408[/TD]
[TD]yes[/TD]
[TD]RDO Equipment - Fargo - Corp.[/TD]
[TD]T-8[/TD]
[TD][/TD]
[TD]TCO690M-G3[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD="align: right"]8/9[/TD]
[TD]TCE40994[/TD]
[TD="align: right"]7409[/TD]
[TD]yes[/TD]
[TD]Talco Enterprises, LLC - Nottingham[/TD]
[TD]T-4[/TD]
[TD][/TD]
[TD]TCO690M-G3[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/9[/TD]
[TD]TCE41004[/TD]
[TD="align: right"]7410[/TD]
[TD]yes[/TD]
[TD]Maicom, LLC. - North Andover[/TD]
[TD]T-4[/TD]
[TD][/TD]
[TD]TCO920M-G3[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/10[/TD]
[TD]TCE41018[/TD]
[TD="align: right"]7411[/TD]
[TD]yes[/TD]
[TD]Ag Resources - Maddock[/TD]
[TD]T-8[/TD]
[TD][/TD]
[TD]Custom[/TD]
[TD]x[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/10[/TD]
[TD]TCE41015[/TD]
[TD="align: right"]7412[/TD]
[TD]yes[/TD]
[TD]Growmark, Inc. - Bloomington[/TD]
[TD]T-5[/TD]
[TD]584[/TD]
[TD]TCO990EV[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/13[/TD]
[TD]TCE41023[/TD]
[TD="align: right"]7413[/TD]
[TD]yes[/TD]
[TD]Barker Implement Co. - Shelbyville[/TD]
[TD]T-5[/TD]
[TD][/TD]
[TD]TCO500[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/13[/TD]
[TD]TCE41043[/TD]
[TD="align: right"]7414[/TD]
[TD]yes[/TD]
[TD]CNH - N&S Tractor - Turlock[/TD]
[TD]T-7[/TD]
[TD][/TD]
[TD]Custom[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/13[/TD]
[TD]TCE41022[/TD]
[TD="align: right"]7415[/TD]
[TD]yes[/TD]
[TD]Wheeler Machinery Co. - Salt Lake City[/TD]
[TD]T-7[/TD]
[TD][/TD]
[TD]TCO920M-G3[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/13[/TD]
[TD]TCE41028[/TD]
[TD="align: right"]7416[/TD]
[TD]yes[/TD]
[TD]Wheeler Machinery Co. - Salt Lake City[/TD]
[TD]T-7[/TD]
[TD][/TD]
[TD]TCO920M-G3[/TD]
[TD]x[/TD]
[TD]X[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]
_{^{<strike>
</strike>}}

 

[amyb2703]

The source data is pulled from our MRP database into a tab in the workbook and looks like this. The field "SO Number" is the MATCH part of the formula - the "Data!$B$2:$B$2000" portion of the formula. - on my main tab, I key in the SO Number and it looks for the lines that include a match to it, then it marks "X" into columns that also have a corresponding match to the item in the column (the "Data!$I$2:$I$2000="TCO302" part of the formula).

[TABLE="width: 2018"]
<tbody>[TR]
[TD][/TD]
[TD]SO Number[/TD]
[TD]JOBSTATS[/TD]
[TD]Territory ID[/TD]
[TD]Dealer[/TD]
[TD]Order Date[/TD]
[TD]Order Week[/TD]
[TD]Promise Date[/TD]
[TD]Item[/TD]
[TD]Item Description[/TD]
[TD]REF1[/TD]
[TD]City[/TD]
[TD]SORTNO[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD]TCE41379TCO202[/TD]
[TD]TCE41379[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]KDK Sales - Washington[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/28/2018[/TD]
[TD]TCO202-GY[/TD]
[TD]Standard Paint Color - GRAY[/TD]
[TD]T-5[/TD]
[TD]Washington[/TD]
[TD="align: right"]20[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41379TCO514[/TD]
[TD]TCE41379[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]KDK Sales - Washington[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/28/2018[/TD]
[TD]TCO514[/TD]
[TD]8 Bolt Steel / Standard Tires[/TD]
[TD]T-5[/TD]
[TD]Washington[/TD]
[TD="align: right"]30[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41379TCO990[/TD]
[TD]TCE41379[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]KDK Sales - Washington[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/28/2018[/TD]
[TD]TCO990EV[/TD]
[TD]EV990 BASE PARTS[/TD]
[TD]T-5[/TD]
[TD]Washington[/TD]
[TD="align: right"]10[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO102[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO102[/TD]
[TD]Rear Utility Box[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]20[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO202[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO202-WT[/TD]
[TD]Custom Paint Color - WHITE[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]80[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO303[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO303[/TD]
[TD]FUEL REEL, 35' STANDARD[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]40[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO305[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO305[/TD]
[TD]DEF REEL, 35' STANDARD[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]50[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO307[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO307[/TD]
[TD]10 Micron Filter System[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]90[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO502[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO502[/TD]
[TD]Electric / Hydraulic Jack[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]60[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO750[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO750[/TD]
[TD]FST750 BASE PARTS LIST[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]10[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO904[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO904[/TD]
[TD]Signature Package[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]30[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41381TCO907[/TD]
[TD]TCE41381[/TD]
[TD]ORDERED[/TD]
[TD]T-5[/TD]
[TD]Eldon C. Stutsman, Inc. - Hills[/TD]
[TD="align: right"]8/27/2018[/TD]
[TD]35[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD]TCO907[/TD]
[TD]STANDARD FENDERS AND ROCK GUARD[/TD]
[TD]T-5[/TD]
[TD]Hills[/TD]
[TD="align: right"]70[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41365ADT502[/TD]
[TD]TCE41365[/TD]
[TD]ORDERED[/TD]
[TD]T-2[/TD]
[TD]Yancey Brothers, Co. - Austell[/TD]
[TD="align: right"]8/24/2018[/TD]
[TD]34[/TD]
[TD="align: right"]10/26/2018[/TD]
[TD]ADT5026[/TD]
[TD]PINTLE HITCH (UNDER 14K GVWR)[/TD]
[TD]T-2[/TD]
[TD]Austell[/TD]
[TD="align: right"]80[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TCE41365Freigh[/TD]
[TD]TCE41365[/TD]
[TD]ORDERED[/TD]
[TD]T-2[/TD]
[TD]Yancey Brothers, Co. - Austell[/TD]
[TD="align: right"]8/24/2018[/TD]
[TD]34[/TD]
[TD="align: right"]10/26/2018[/TD]
[TD]Freight[/TD]
[TD]Freight Delivery Charge[/TD]
[TD]T-2[/TD]
[TD]Austell[/TD]
[TD="align: right"]100[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[amyb2703]

*

 

[amyb2703]

Still hoping for some assistance or insight on this one! Thanks for stopping!

 

[amyb2703]

Still looking for some assistance....h

ave a great holiday weekend! ​

Thanks!

 

[SpillerBD]

Your second issue is that the rows for your match & index ranges should all be increased. You could also use Named ranges or Table[Fields]
I prefer Table[Fields] since they expand. Named ranges can expand for you if you follow the tricks.

 

[raymangoose]

I have bunch of Index(match formulas and would like to add Iferror in front of all of them at once. Is there a way?

 

[amyb2703]

My problem is that if I increase them, even updating the formula manually to go to $I$8000, it doesn't actually do it. I can refresh data, save and reopen, and it will say my new range in the formula, but not actually recognize the data. Any ideas on why that might be? In the meantime, I have changed my sort on that table to decrease the likelihood that open orders remain in the range that is recognized.

 

[Aladin Akyurek]

Which column is numeric? And what is the sheet name?