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

A

amyb2703

New Member
Joined
Aug 24, 2018
Messages
18
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!
 
Sort by date Sort by votes
Range 14 I believe is Column N that holds the "X" I'm looking for.

Data tab:

A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
SO NumberJOBSTATSTerritory IDDealerOrder DateOrder WeekPromise DateItemItem DescriptionREF1CitySORTNOColumn1
2
TCE32075M10002TCE32075TEMPLATE4/24/2017174/24/2017M100021FST990 BASE PARTS LIST10X
3
TCE41550SLT-G3TCE41550ORDEREDT-99STOCK9/4/20183611/27/2018SLT-G3SLT Trailer - Custom Layout - G3T-9910X
4
TCE41550TCO103TCE41550ORDEREDT-99STOCK9/4/20183611/27/2018TCO103G3-Rear Utility BoxT-9920X
5
TCE41550TCO915TCE41550ORDEREDT-99STOCK9/4/20183611/27/2018TCO91516CFM Compressor with 50' Air reel - UB KITT-9930X
6
TCE41550TCO502TCE41550ORDEREDT-99STOCK9/4/20183611/27/2018TCO502Electric / Hydraulic JackT-9940X
7
TCE41550TCO903TCE41550ORDEREDT-99STOCK9/4/20183611/27/2018TCO903MTT-G3 100 Gal DEF Tank and PumpT-9950X

<tbody>
</tbody>
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
What must happen if this formula bit you posted

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

succeeds?
 
Upvote 0
Data Tab info - I have filtered for just a small section

A
B
C
D
E
F
G
H
I
J
K
L
M
N
SO NumberJOBSTATSTerritory IDDealerOrder DateOrder WeekPromise DateItemItem DescriptionREF1CitySORTNOColumn1
TCE41494TCO500TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO500FST500 BASE PARTS LISTT-7Madera10X
TCE41494TCO102TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO102Rear Utility BoxT-7Madera20X
TCE41494TCO104TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO104Can & Towel RackT-7Madera30X
TCE41494TCO302TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO30240 GPM Gas Powered PumpT-7Madera40X
TCE41494TCO307TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO30710 Micron Filter SystemT-7Madera50X
TCE41494TCO303TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO303FUEL REEL, 35' STANDARDT-7Madera60X
TCE41494TCO510TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO5106 Bolt Aluminum / Standard TiresT-7Madera70X
TCE41494TCO501TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO501STANDARD JACK ASSEMBLYT-7Madera80X
TCE41494TCO806TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO806Front Box Five Drawer Tool ChestT-7Madera90X
TCE41494TCO907TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO907STANDARD FENDERS AND ROCK GUARDT-7Madera100X
TCE41494TCO202TCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO202-WTCustom Paint Color - WHITET-7Madera110X
TCE41494FreighTCE41494ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018FreightFreight Delivery ChargeT-7Madera120X
TCE41493TCO500TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO500FST500 BASE PARTS LISTT-7Madera10X
TCE41493TCO102TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO102Rear Utility BoxT-7Madera20X
TCE41493TCO914TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO914Comp/Gen w/ 50' Air/Elec Combo ReelT-7Madera30X
TCE41493TCO302TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO30240 GPM Gas Powered PumpT-7Madera40X
TCE41493TCO307TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO30710 Micron Filter SystemT-7Madera50X
TCE41493TCO303TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO303FUEL REEL, 35' STANDARDT-7Madera60X
TCE41493TCO510TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO5106 Bolt Aluminum / Standard TiresT-7Madera70X
TCE41493TCO501TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO501STANDARD JACK ASSEMBLYT-7Madera80X
TCE41493TCO907TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO907STANDARD FENDERS AND ROCK GUARDT-7Madera90X
TCE41493TCO202TCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO202-GYStandard Paint Color - GRAYT-7Madera100X
TCE41493FreighTCE41493ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018FreightFreight Delivery ChargeT-7Madera110X
TCE41492TCO750TCE41492ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO750EVEV750 BASE PARTST-7Madera10X
TCE41492TCO202TCE41492ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO202-GYStandard Paint Color - GRAYT-7Madera20X
TCE41492TCO510TCE41492ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO5106 Bolt Aluminum / Standard TiresT-7Madera30X
TCE41492FreighTCE41492ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018FreightFreight Delivery ChargeT-7Madera40X
TCE41491TCO500TCE41491ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO500FST500 BASE PARTS LISTT-7Madera10X
TCE41491TCO302TCE41491ORDEREDT-7Midland Tractor - Madera8/30/20183510/4/2018TCO30240 GPM Gas Powered PumpT-7Madera20X

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Results expected - as new orders are placed, I key the SO Number into column B. Then each cell in the row from columns I through AT has a formula looking for a particular item. When a match per the formula is found, an "X" is placed in the corresponding cell. The example I have been using looking for item TCO302 is for the results in Column I.

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
ABACADAEAFAGAH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
AR
AS
AT
Order DateSO #Bld #ScheduledCustomerRepVIN #Model ELEC GAS PUMPSOLAR BATTERY CHARGER100G DEF SYSTEM WITH REELALUMINUM WHEELSFIELD MAX TIRESSIGNATURE OPTION PACKAGE WORKSIGHT LIGHT TOWER ELECTRIC/HYD JACK30 G AUX OIL TANK 3GPMSTAINLESS STEEL FENDER AND TRIMFIVE DRAWER TOOL CHEST PRO OPTION PACKAGE REAR UTILITY BOXCAN AND TOWEL RACKUTILITY BOX FIVE DRAWER TOOL CHESTUTILITY OPTION PACKAGE COMP+GEN WITH 50' COMBO REELWELDER+COMP+GEN WITH 50' COMBO REELcompressor only WORK BENCH WITH VISETWO DRAWER LOW BOY TOOL CHEST50' FUEL HOSE REEL UPGRADE50' DEF REEL UPGRADESTATIC DISCHARGE REEL4 MICRON FUEL FILTER UPGRADEDIGITAL FUEL METER FOR ELECTRIC PUMPDIGITAL FUEL METER FOR GAS PUMP110V DEF HEATERDIGITAL DEF METERCUSTOM COLORFIELD MAX SPARE TIRE KITSPARE TIRE KITGEAR DRIVE 8 GPM PUMP UGRADE FOR AUX OILSPILL CONTAINMENT KITGREASE KIT
302, 332, 333702902, 903802, 510, 512, 518, 520804, 508, 512, 516, 520904 704502906908, 965806910 102, 103104808912 913916915, 914918810304, 340306812, 813308, 345310312814314TCO202920920, 921, 922, 923316924816
ORDEROPTIONS
8/30TCE41494 Midland Tractor - MaderaT-7 TCO500X X XX WHITE

<colgroup><col><col><col><col><col><col><col><col><col span="6"><col><col span="6"><col><col span="3"><col><col><col span="7"><col><col span="5"><col><col span="5"></colgroup><tbody>
</tbody>
 
Upvote 0
MATCH(1,(Data!$B$2:$B$2000=B1026)*(Data!$I$2:$I$2000="TCO302"),0)

returns a position if successful.


Questions:


1. What do we do with the foregoing MATCH result? Feed it to an INDEX bit?

2. Does TCO302 occur literally in Data!$I$2:$I$2000?
 
Last edited:
Upvote 0
1. When there is a match, it fills an "X" (or whatever is in column N) into the cell.
2. I believe that TCO302 occurs literally in the Data table; it is updated via a link to tables in our order system.

(Thanks so much for all of the help so far. I have learned a lot!)
 
Upvote 0
amyb2703 said:
1. When there is a match, it fills an "X" (or whatever is in column N) into the cell.

[…]
Click to expand...

You mean INDEX must retrieve whatever is in the N column at the position MATCH returns, right? That is, if match returns 7, the 7th item from the N range must be returned...
 
Upvote 0
That is where it gets beyond my experience w/ this stuff I'm afraid. Currently the Data table is set-up to fill in the N cell of a new line w/ X, so if there is a match, it always finds X to return.
 
Upvote 0
amyb2703 said:
That is where it gets beyond my experience w/ this stuff I'm afraid. Currently the Data table is set-up to fill in the N cell of a new line w/ X, so if there is a match, it always finds X to return.
Click to expand...

Do you have any formula in column N?
 
Upvote 0
No excel formula in column N - that whole tab is connected to the database and updates from there, and is set-up to include an X in column N when the data is updated - I am not sure how to see what it does.
 
Upvote 0
amyb2703 said:
No excel formula in column N - that whole tab is connected to the database and updates from there, and is set-up to include an X in column N when the data is updated - I am not sure how to see what it does.
Click to expand...

If the following is not what you want, that is, confirmed with control+shift+enter:

=INDEX(Data!$N$2:$N$2000,MATCH(1,(Data!$B$2:$B$2000=B1026)*(Data!$I$2:$I$2000="TCO302"),0))

you must really sit back and think what you want to achieve.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Index/Match | Index/Small Formula
Replies
5
Views
343
Eric W
Eric W
G
  • Question
Index / Match multiple criteria and multiple results
Replies
0
Views
564
Glasgowsmile
G
C
  • Question
Stock inventory - count unique sequences from barcode
Replies
0
Views
372
ChemistryBMS
C
B
  • Solved
Formula help INDEX and MATCH Please :)
2
Replies
11
Views
567
BeckyLH88
B
E
  • Question
index and match in a range
Replies
0
Views
491
Excel_Legend
E

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top