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]

None of the fields are numeric....all alphanumeric or dates. And the sheet w/ all the raw data is called Data.

 

[Aladin Akyurek]

None of the fields are numeric....all alphanumeric or dates. And the sheet w/ all the raw data is called Data.

What is the current range of Data?

 

[amyb2703]

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)),"")

Basically from line 2 through 2000. The

MATCH(1,(Data!$B$2:$B$2000=B771)​

is looking up the order number. Once it finds a match there, it looks in column I (Data!$I$2:$I$2000="

TCO302​

")​

for the sales code.
​

 

[Aladin Akyurek]

Would you answer the question of post #12 ?

 

[amyb2703]

I apologize - I mis-read the question. Data tab currently holds information through field N26700.

 

[Aladin Akyurek]

I apologize - I mis-read the question. Data tab currently holds information through field N26700.

A1:N26700 with headers A1:N1?

 

[amyb2703]

A1:N26700 with headers A1:N1?

Yes, that is correct.

 

[Aladin Akyurek]

Yes, that is correct.

1. Define Lrow in Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),Data!$A:$A)

2. Define Arange (or a different name that is more meaningful to you) as referring to:

=Data!$A$2:INDEX(Data!$A:$A,Lrow)

3. Define Brange as referring to:

=Data!$B$2:INDEX(Data!$B:$B,Lrow)

Repeat the foregoing procedure for Crange, …, Nrange.

Define also DATA as referring to:

=Data!$A$2:INDEX(Data!$N:$N,Lrow)

These definitions are generally referred to as dynamic named ranges. They are sensitive to record additions to
and deletions from the data area A:N of Data.Thus, there is no need re-edit the formulas which use any of these
ranges.


By the way,

INDEX(DATA,0,1)

is equivalent to Arange and can be also used as such in formulas.

 

[amyb2703]

1. Define Lrow in Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),Data!$A:$A)

2. Define Arange (or a different name that is more meaningful to you) as referring to:

=Data!$A$2:INDEX(Data!$A:$A,Lrow)

3. Define Brange as referring to:

=Data!$B$2:INDEX(Data!$B:$B,Lrow)

Repeat the foregoing procedure for Crange, …, Nrange.

Define also DATA as referring to:

=Data!$A$2:INDEX(Data!$N:$N,Lrow)

These definitions are generally referred to as dynamic named ranges. They are sensitive to record additions to
and deletions from the data area A:N of Data.Thus, there is no need re-edit the formulas which use any of these
ranges.


By the way,

INDEX(DATA,0,1)

is equivalent to Arange and can be also used as such in formulas.

I believe I got the ranges set-up correctly using the Name Manager. I have not been able to get the formula to work though. I am not getting errors or warnings, but I am not getting the results I expect either.

Original Formula:
=IFERROR(IF(N1026="X","X",INDEX(Data!$A$2:$N$2000,MATCH(1,(Data!$B$2:$B$2000=B1026)*(Data!$I$2:$I$2000="TCO302"),0),14)),"")

My version:
=IFERROR(IF(N1026="X","X",INDEX(Lrow,MATCH(1,(SONumber=B1026)*(Item="TCO302"),0),14)),"")

SONumber = =Data!$B$2:INDEX(Data!$B:$B,Lrow)
Item = =Data!$I$2:INDEX(Data!$I:$I,Lrow)

 

[Aladin Akyurek]

Which range is 14?