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:

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

<tbody>
</tbody>

_{^{<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).

SO Number

JOBSTATS

Territory ID

Dealer

Order Date

Order Week

Promise Date

Item

Item Description

REF1

City

SORTNO

Column1

TCE41379TCO202

TCE41379

ORDERED

T-5

KDK Sales - Washington

8/27/2018

35

8/28/2018

TCO202-GY

Standard Paint Color - GRAY

T-5

Washington

20

X

TCE41379TCO514

TCE41379

ORDERED

T-5

KDK Sales - Washington

8/27/2018

35

8/28/2018

TCO514

8 Bolt Steel / Standard Tires

T-5

Washington

30

X

TCE41379TCO990

TCE41379

ORDERED

T-5

KDK Sales - Washington

8/27/2018

35

8/28/2018

TCO990EV

EV990 BASE PARTS

T-5

Washington

10

X

TCE41381TCO102

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO102

Rear Utility Box

T-5

Hills

20

X

TCE41381TCO202

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO202-WT

Custom Paint Color - WHITE

T-5

Hills

80

X

TCE41381TCO303

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO303

FUEL REEL, 35' STANDARD

T-5

Hills

40

X

TCE41381TCO305

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO305

DEF REEL, 35' STANDARD

T-5

Hills

50

X

TCE41381TCO307

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO307

10 Micron Filter System

T-5

Hills

90

X

TCE41381TCO502

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO502

Electric / Hydraulic Jack

T-5

Hills

60

X

TCE41381TCO750

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO750

FST750 BASE PARTS LIST

T-5

Hills

10

X

TCE41381TCO904

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO904

Signature Package

T-5

Hills

30

X

TCE41381TCO907

TCE41381

ORDERED

T-5

Eldon C. Stutsman, Inc. - Hills

8/27/2018

35

8/31/2018

TCO907

STANDARD FENDERS AND ROCK GUARD

T-5

Hills

70

X

TCE41365ADT502

TCE41365

ORDERED

T-2

Yancey Brothers, Co. - Austell

8/24/2018

34

10/26/2018

ADT5026

PINTLE HITCH (UNDER 14K GVWR)

T-2

Austell

80

X

TCE41365Freigh

TCE41365

ORDERED

T-2

Yancey Brothers, Co. - Austell

8/24/2018

34

10/26/2018

Freight

Freight Delivery Charge

T-2

Austell

100

X

<tbody>
</tbody>

 

[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?