Problem with match function

[nander]

In this spreadsheet test1 I have 5 worksheets. I want in the Maste
worksheet to be the destination worksheet for the data in the othe
worksheets. the data for the 3 columns MULT Contract Price.Point come
from Type, Sales 01, Sales 02 & Pointer.

so in the master worksheet, the value of cell D1 should be from the C
in cell C2 compared to the CUST CLASS column in worksheet TYPE. AN
worksheet SALES01 . I would like the CONTRACT AND PRICE POINT values i
the MASTER worksheet to come from SALES01. what do I need to do t
correct the formula that is in cell D2 and what do i need in cell E2
F2

+-------------------------------------------------------------------
|Filename: test1.zip
|Download: http://www.excelforum.com/attachment.php?postid=4653
+-------------------------------------------------------------------

 

[mrice]

Your sub formula MATCH(C2,{0,500,2500,7500},1) appears to be trying to
look up a text value in an array of numbers and giving the error.

 

[nander]

Okay then can you suggest a corrected formula. I was given this formula
and I really don't understand how it works.

 

[Peo Sjoblom]

You probably have a better chance of getting an answer if you post a sample
of the data (as text in the body of your message) since many of the regulars
don't open workbooks from strangers due to virus risk.

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[nander]

I can cut and past parts of the spreadsheet but it will appear
unstructured.

CUST VOLUME
CLASS > 7,500 7,499-2,500 2,499 - 500 < 500
25 NEW CUSTOMER 0.50 0.50 0.55 0.60
0Z RETAIL 0.70 0.70 0.70 0.70
AG AGRICULTURE & FARMING 0.60 0.60 0.60 0.60
AP APARTMENTS 0.50 0.50 0.55 0.60
AS APPLIANCE SERVICE 0.50 0.50 0.50 0.55
AT ATTRACTIONS 0.55 0.55 0.55 0.55
BR JOHNSTONE BRANCH SALES 0.45 0.45 0.45 0.45
CH CHURCHES 0.60 0.60 0.60 0.60
EL ELECTRICAL 0.50 0.50 0.55 0.60
EM EMPLOYEE 0.50 0.50 0.50 0.50
FX O/E FAX# ADDED CUST.MAST 0.50 0.50 0.55 0.60
GA GAS COMPANY 0.50 0.50 0.55 0.60
GC GENERAL CONTRACTORS 0.50 0.55 0.55 0.60
GR GROCERY STORES 0.55 0.55 0.55 0.55
GV CITY, COUNTY, STATE & FED 0.55 0.55 0.55 0.60
HA HTG AC & APPL 0.45 0.47 0.50 0.55
HL HOSPITALS & NURSING HOMES 0.50 0.50 0.55 0.60
HO HOTELS, MOTELS & INNS 0.50 0.50 0.55 0.60

CUST# CUST.NAME TYP CL MULT Contract PRICE.POINT CITY STATE ZIP.CODE CO#
100162 FAULTON DOUGLAS HV HV #N/A #N/A ABBEVILLE AL 36310 01
102038 CURTIS APPLIANCE REPAIR & AS AS ABBEVILLE AL 36310 01
103715 GREAT SOUTHERN WOOD PRESE IN IN ABBEVILLE AL 36310 01
104306 HENRY CO NURSING HOME HL HL ABBEVILLE AL 36310 01
104312 HENRY CO BOE SC SC ABBEVILLE AL 36310-0635 01
104313 HENRY CO BRD OF EDUCATION SC SC ABBEVILLE AL 36310 01
104369 AW HERNDON OIL CO INC MC MC ABBEVILLE AL 36310 01
104390 HERNDON OIL CORPORATION MC MC ABBEVILLE AL 36310-0655 01
105340 J&J REPAIR & A/C SERVICE 01 HV ABBEVILLE AL 36310 01
108086 TELEDYNE ABBEVILE IN IN ABBEVILLE AL 36310 01
122027 ANDALUSIA HSNG AUTHORITY GV GV ANDALULSIA AL 36420-4791 01
122030 ANDALUSIA MANOR HL HL ANDALULSIA AL 36420-0000 01
100175 ALABAMA ELECTRIC CO-OP GV GV ANDALUSIA AL 36420-0550 01
101891 COVINGTON CO SCHOOLS SC SC ANDALUSIA AL 36420-0460 01
102630 DRAPER HEATING A/C & REF HR HR ANDALUSIA AL 36420 01
102882 BUTLER AIR CONDITIONING 01 HV ANDALUSIA AL 36420 01
105400 KILPATRICK SHEET METAL HR HR ANDALUSIA AL 36420 01
105650 LLOYD'S REFRIGERATION 03 HR ANDALUSIA AL 36420 01
CUST# CUST.NAME TYP CL SALES 12MO MULT Contract.. CO# ABC
108065 TED'S HEATING & COOLING 0.00 109,936.38 0.45 HVAC 1 A
100211 ANDERSON'S REPAIR SERVIC 0.00 108,907.02 0.45 SHVAC 1 A
105104 JIMMY'S H&C - DOTHAN 0.00 100,949.72 0.45 LHVAC 1 A
106140 NORTH AIR 0.00 69,851.83 0.45 HVAC 1 A
1111 CASH - DEALER/CONTACTOR 0.00 67,479.19 0.45 1 A
107530 SNELLGROVE & CAYLOR H&C 0.00 64,145.94 0.45 LHVAC 1 A
100069 KELLY'S HEATING & COOLIN 0.00 61,687.40 0.45 HVAC 1 A
109540 MERRITT'S (JERRY) H&C 0.00 53,227.93 0.45 HVAC 1 A
103088 FAILS HEATING & A/C 0.00 52,454.89 0.45 HVAC 1 A
110375 BLUDSWORTH SERVICE COMPA 0.00 43,614.19 0.45 1 A
104789 HOUSTON CO BOE 0.00 41,901.52 0.45 1 A
101175 COMFORT SYSTEMS U.S.A. 0.00 39,202.61 0.45 LHVAC 1 A
104579 HOLLIS REFRG & APPL SVC 0.00 38,781.96 0.45 SHVAC 1 A
109178 WOODALL H&C INC. 0.00 37,716.74 0.45 LHVAC 1 A
109997 MOBLEY REF & ELEC SVC IN 0.00 36,539.33 0.45 SHVAC 1 A
106889 RELIABLE SERVICE CO 0.00 35,988.36 0.45 REF 1 A
CUST# CUSTOMER NAME CITY.STATE.ZIP PRICE.POINT ABC CO#
1111 CASH - DEALER/CONTACTOR DOTHAN, AL 36301 0 A 1
10051 JOHNSTONE #51 KNOXVILLE, TN 37950 -1 D 1
100018 JOHNSTONE OF MEMPHIS MEMPHIS, TN 38112 -1 D 1
100019 JOHNSTONE OF SAVANNAH SAVANNAH, GA 31401 -1 D 1
100020 JOHNSTONE OF MOBILE MOBILE, AL 36609 -1 D 1
100021 JOHNSTONE OF NEW ORLEANS HARAHAN, LA 70183-0716 -1 D 1
100022 JOHNSTONE OF PENSACOLA PENSACOLA, FL 32505 -1 D 1
100023 JOHNSTONE OF TALLAHASSEE TALLAHASSEE, FL 32304 -1 D 1
100024 JOHNSTONE OF NASHVILLE NASHVILLE, TN 37210 -1 D 1
100026 JOHNSTONE OF BIMINGHAM BIRMINGHAM, AL 35222 -1 D 1
100027 JOHNSTONE OF GAINSVILLE GAINESVILLE, FL 32609 -1 D 1
100029 JOHNSTONE NAPERVILLE NAPERVILLE, IL 60563 -1 D 1
100033 JOHNSTONE SUPPLY #37 ST.PETERSBURG, FL 33714 -1 D 1
100035 JOHNSTONE OF HUNTSVILLE HUNTSVILLE, AL 35816 -1 D 1
100036 JOHNSTONE OF JACKSONVILL JACKSONVILLE, FL 32204 -1 D 1
100039 WASHINGTON CO KENNEL CLU EBRO, FL 32437 0 D 1
100050 JOHNSTONE SUPPLY CLARKVL CLARKSVILLE, TN 37040 -1 D 1
100063 JOHNSTONE SUPPLY W PALM WEST PALM BEACH, FL 33309 -1 D 1
100064 JOHNSTONE BATON ROUGE #1 BATON ROUGE, LA 70816 -1 D 1
100068 SWEETWATER APARTMENTS DOTHAN, AL 36305 0 C 1
100089 SOUTHERN PIPE & SUPPLY FLORENCE, AL 35630 0 D 1
100107 JOHNSTONE GULFPORT 220 GULFPORT, MS 39501 -1 D 1
100108 JOHNSTONE MDC MEMPHIS, TN 38118-7432 -1 D 1
100111 BAY ST JOSEPH CARE PORT ST. JOE, FL 32456 0 C 1