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.
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!
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!
