Tomkat is trying to cap mileage paid based on production. The mileage cap varies based on production and site. Read this newsgroup post for details if you like. This newsgroup post is where it all started.
This calls for a VLOOKUP/MATCH combo if I ever saw it.
The formula in E15 (and filled down column E) is
MATCH: The MATCH function returns the column that matches the site entered. It’s used as the col_index_num argument of VLOOKUP, that is, which column VLOOKUP retrieves. You have to add two to the result because the range of “Sites” starts two cell right of the vlookup range.
VLOOKUP: The left-most column of the mileage cap table is filled with the lowest number of the appropriate range of numbers. Column B shows the range of numbers, but it’s just there for informational purposes. Because there is no fourth argument in the VLOOKUP, it will find the largest number that is not greater than the number it’s looking for. If, as in row 15, we’re looking for 342, it will find 300 because that’s the largest number in the list that’s not greater than 342.
MIN: The whole thing is wrapped in a MIN because it’s a cap. If the person claims less than the cap, then that value should be used, not the cap.
I hope that helps Tomkat.
Dick,
What I often find myself having to do is lookup from a huge table values of three of more critera. And to do that the only way I know how is through SUMPRODUCT. Example:
=SUMPRODUCT((‘MY06 Freq’!$D$8:$D$5028=Calcs!$B4)*(‘MY06 Freq’!$C$8:$C$5028=Calcs!C$2)*(‘MY06 Freq’!$G$8:$G$5028))
Which would give me array’s of 1 * 1 * the value in the column I want. What are you’re thoughts on this method. Is there a better way?
-Steve
Thank you for this. The Vlookup was the first one I tried as you can see by my post on 4/18/06 I was doing a couple of things wrong which make sense now. First I was trying to incorporate a less than rather than MIN as I was unfamiliar with MIN or at least did not have a good understanding of its principles. Second I did not think to break my table in smaller incriments than what I was actually using. I wish I hadn’t gotten side tracked with the whole CHOOSE thing and I may have gotten a little closer on my own. “OH WELL” I learned a few things in the process. Thanks again for your excellent help.
This is about the many Excel ways to skin a cat. When I have a need for entries like Site1, Site2, Site3, I tend to use a custom number format such as “Site”0. From there I would use a
CHOOSE(C15,3,4,5) in place of the MATCH()+2.
Which approach has the lower overhead for the spreadsheet?
…Michael
I don’t think you need the CHOOSE with Michael’s (reply#3) technique; just C15+2 for col_index_num.
Brett –
Even better!
…mrt
Always looking for shorter formulas for same job I came up with this variation on the OP:
=MIN(D15,VLOOKUP(B15,$A$2:$E$11,RIGHT(C15)+2))
Yes, it’s a bit of a kludge, but I think still valid. A handy use of the default behaviour of RIGHT in assuming a “1? if a number is not supplied.
Speaking about VLOOKUP…
VLOOKUP( lookup_value, table_array, col_index_num, range_lookup)
The help indicates that “If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value”
I noticed that this is not true when VLOOKUP_VALUE is a reference to a calculated value e.g. VLOOKUP (A3,… where cell A3 contains a formula e.g. =A1&A2.
When VLOOKUP_VALUE is a constant or a reference to a constant the function works as explained in the help.
Any ideas how to overcome this “feature”?
10x
My mistake. Works. The array was not sorted…
i need to populate a spreadsheet based on looking at a horizontal string of variables and a vertical string of variables and retrieving the intersection of those two points for a very large worksheet of data. is there a vlookup hlookup combo that will work here? a two variable lookup.
i’m using a data validation list with months and when a given month is chosen i want the summary page to populate the given data from one of the monthly worksheets? any ideas besides a huge if statement. i’d heard the choose formula is useful here.
Hi, I’m having a problem with vlookup, indexing and matching because I have to many variables due to constants and variables in three different columns. This is due to Cost Centres(CC), Dept, Nominal Coding(NC), to give budgeted amounts.see Table below. I want to lookup the amounts for each unique NM,CC,& Dept. The spreadsheet is much bigger than this, and I need to have the figures on another worksheet.
CN CC DEPTAMOUNT
606110003008005498.00
606110003609202185.00
6061200030080058401.00
6061200036092024958.00
6061400030080018625.00
606140003609208011.00
60620000200505258.00
60620000200563123.00
60620000200585140.00
60620000200595234.00
60620000201556205.00
6062000020156036.00
60620000220505392.00
606200002215851055.00
60620000223560917.00
606200003005208000.00
6062000030052523.00
606200003008008643.00
6062000032090012500.00
60631000300800197.00
606310003008304200.00
606320003008005150.00
60632000360920515.00
606400002005855000.00
60640000201560167.00
606400002205052.00
606400002235600.00
606400002857500.00
6064000030052074.00
606400003008007922.00
606830002005853313.00
60683000201560196.00
60683000202560786.00
Hi Walt –
If I understand correctly, you want to separately sum all the CN entries, the CC entries, and the DEPT entries. Use SUMIF(). Assuming your data are in A:D, then for example in F2:
=SUMIF(A:A,A2,$D:$D)
and fill right. (=7683 =116733=104436 for 60611000,300,800)
…mrt
I am trying to look up an intersection in excel based on two variables. This is to calculate freight rates. on cell is for the row, (ship to zone) and the other variable cell is for the weight of the package (this would be a look up in the column. I can’t figure out what formula to use. Can you help? here is the spreadsheet…
ENTER DESTINATION ZIP CODE Weight (in Lbs)2345678910111213141516
5251 $6.03 $8.01 $9.79 $10.89 $11.82 $12.52 $12.87 $13.27 $13.27 $13.44 $13.44 $10.39 $10.39 $10.39 $10.39
2 $6.34 $8.72 $11.06 $12.05 $13.35 $13.79 $14.34 $14.47 $14.47 $14.72 $14.72 $11.32 $11.32 $11.32 $11.32
ZONE3 $7.08 $9.17 $12.24 $13.34 $14.80 $15.15 $15.68 $16.13 $16.13 $18.61 $18.61 $12.57 $12.57 $12.57 $12.57
74 $7.63 $9.61 $13.27 $14.60 $16.17 $16.50 $17.15 $17.62 $17.62 $20.16 $20.16 $13.94 $13.94 $13.94 $13.94
5 $7.81 $10.11 $14.49 $15.97 $17.52 $17.87 $18.50 $19.05 $19.05 $21.56 $21.56 $15.15 $15.15 $15.15 $15.15
ENTER WEIGHT6 $8.39 $10.59 $15.68 $17.23 $18.96 $19.13 $19.96 $20.41 $20.41 $23.11 $23.11 $16.70 $16.70 $16.70 $16.70
437 $8.77 $11.12 $16.78 $18.50 $20.31 $20.60 $21.31 $21.76 $21.76 $24.61 $24.61 $17.20 $17.20 $17.20 $17.20
8 $8.94 $11.65 $17.87 $19.68 $21.68 $22.04 $22.68 $23.11 $23.11 $25.76 $25.76 $18.96 $18.96 $18.96 $18.96
HERE IS YOUR RATE9 $9.22 $12.19 $18.86 $20.95 $23.03 $23.49 $24.13 $24.56 $24.56 $27.34 $27.34 $19.95 $19.95 $19.95 $19.95
#N/A10 $9.24 $12.74 $19.75 $22.04 $24.38 $24.86 $25.57 $26.01 $26.01 $28.80 $28.80 $21.05 $21.05 $21.05 $21.05
11 $9.92 $13.27 $20.78 $23.23 $25.74 $26.31 $26.94 $27.46 $27.46 $30.07 $30.07 $22.03 $22.03 $22.03 $22.03
12 $10.31 $13.80 $21.89 $24.31 $27.01 $27.76 $28.39 $28.80 $28.80 $30.72 $30.72 $22.93 $22.93 $22.93 $22.93
13 $10.47 $14.34 $22.93 $25.41 $28.09 $29.20 $29.75 $30.25 $30.25 $31.55 $31.55 $23.74 $23.74 $23.74 $23.74
14 $10.89 $14.87 $24.11 $26.49 $29.17 $30.49 $31.20 $31.60 $31.60 $32.40 $32.40 $24.56 $24.56 $24.56 $24.56
15 $11.24 $15.40 $24.93 $27.57 $30.25 $31.49 $32.38 $32.87 $32.87 $33.33 $33.33 $25.64 $25.64 $25.64 $25.64
16 $11.46 $15.93 $26.12 $28.57 $31.25 $32.30 $33.20 $33.87 $33.87 $34.60 $34.60 $26.54 $26.54 $26.54 $26.54
17 $11.94 $16.47 $26.86 $29.49 $31.97 $33.02 $33.93 $34.62 $34.62 $35.55 $35.55 $27.36 $27.36 $27.36 $27.36
18 $12.32 $17.00 $27.67 $30.12 $32.68 $33.75 $34.83 $35.46 $35.46 $36.93 $36.93 $28.09 $28.09 $28.09 $28.09
19 $12.59 $17.45 $28.30 $30.75 $33.42 $34.48 $35.75 $36.63 $36.63 $37.88 $37.88 $28.90 $28.90 $28.90 $28.90
20 $12.99 $17.98 $29.04 $31.49 $34.13 $35.30 $36.56 $37.36 $37.36 $40.29 $40.29 $29.80 $29.80 $29.80 $29.80
21 $13.25 $18.35 $29.67 $32.30 $34.87 $36.01 $37.56 $38.23 $38.23 $41.34 $41.34 $30.52 $30.52 $30.52 $30.52
22 $13.64 $18.78 $30.39 $33.02 $35.58 $36.83 $38.38 $39.08 $39.08 $42.27 $42.27 $31.34 $31.34 $31.34 $31.34
23 $13.92 $19.15 $31.12 $33.83 $36.40 $37.65 $39.28 $40.14 $40.14 $42.94 $42.94 $31.97 $31.97 $31.97 $31.97
24 $14.22 $19.50 $31.93 $34.70 $37.38 $38.46 $40.09 $40.91 $40.91 $43.76 $43.76 $32.68 $32.68 $32.68 $32.68
25 $14.60 $19.95 $32.75 $35.40 $38.38 $39.38 $40.91 $42.16 $42.16 $44.61 $44.61 $33.32 $33.32 $33.32 $33.32
26 $14.89 $20.36 $33.65 $36.28 $39.38 $40.38 $41.74 $42.82 $42.82 $45.35 $45.35 $34.13 $34.13 $34.13 $34.13
27 $15.17 $20.75 $34.48 $37.31 $40.46 $41.28 $42.56 $43.71 $43.71 $46.09 $46.09 $34.95 $34.95 $34.95 $34.95
28 $15.55 $21.20 $35.38 $38.38 $41.28 $42.09 $43.46 $44.52 $44.52 $47.04 $47.04 $35.66 $35.66 $35.66 $35.66
29 $15.85 $21.55 $36.30 $39.19 $42.17 $43.01 $44.37 $45.24 $45.24 $48.09 $48.09 $36.30 $36.30 $36.30 $36.30
30 $16.03 $21.91 $37.01 $40.09 $42.99 $43.82 $45.37 $46.05 $46.05 $48.92 $48.92 $37.21 $37.21 $37.21 $37.21
31 $16.42 $22.34 $37.83 $41.01 $43.89 $44.72 $46.19 $46.87 $46.87 $49.77 $49.77 $38.01 $38.01 $38.01 $38.01
32 $16.90 $22.88 $38.64 $41.91 $44.79 $45.64 $47.07 $47.77 $47.77 $50.82 $50.82 $38.93 $38.93 $38.93 $38.93
33 $17.28 $23.44 $39.38 $42.82 $45.70 $46.64 $48.14 $48.85 $48.85 $51.88 $51.88 $39.64 $39.64 $39.64 $39.64
34 $17.67 $23.81 $40.28 $43.72 $46.69 $47.64 $49.12 $49.85 $49.85 $52.83 $52.83 $40.54 $40.54 $40.54 $40.54
35 $18.22 $24.24 $41.19 $44.64 $47.59 $48.63 $50.10 $50.85 $50.85 $53.86 $53.86 $41.46 $41.46 $41.46 $41.46
36 $18.65 $24.63 $42.06 $45.54 $48.50 $49.72 $51.07 $51.83 $51.83 $55.03 $55.03 $42.29 $42.29 $42.29 $42.29
37 $18.68 $25.32 $42.89 $46.45 $49.48 $50.82 $52.15 $52.93 $52.93 $55.86 $55.86 $43.09 $43.09 $43.09 $43.09
38 $18.96 $25.74 $43.64 $47.27 $50.40 $51.98 $53.13 $53.91 $53.91 $56.93 $56.93 $43.82 $43.82 $43.82 $43.82
39 $19.58 $26.17 $44.46 $48.17 $51.38 $53.08 $54.20 $54.99 $54.99 $58.08 $58.08 $44.41 $44.41 $44.41 $44.41
40 $19.95 $26.62 $45.27 $49.08 $52.30 $54.08 $55.26 $56.08 $56.08 $59.14 $59.14 $44.92 $44.92 $44.92 $44.92
41 $20.23 $27.16 $46.09 $49.98 $53.28 $54.98 $56.34 $57.18 $57.18 $60.41 $60.41 $45.82 $45.82 $45.82 $45.82
42 $20.51 $27.69 $47.00 $50.90 $54.36 $56.26 $57.49 $58.34 $58.34 $61.34 $61.34 $46.45 $46.45 $46.45 $46.45
43 $21.06 $28.22 $47.90 $51.80 $55.36 $57.16 $58.66 $59.52 $59.52 $62.30 $62.30 $47.05 $47.05 $47.05 $47.05
44 $21.53 $28.77 $48.72 $52.71 $56.36 $58.16 $59.91 $60.79 $60.79 $63.45 $63.45 $47.65 $47.65 $47.65 $47.65
45 $21.99 $29.39 $49.58 $53.61 $57.44 $59.16 $60.87 $61.77 $61.77 $64.62 $64.62 $48.47 $48.47 $48.47 $48.47
46 $22.43 $30.00 $50.45 $54.53 $58.52 $60.16 $61.94 $62.85 $62.85 $65.43 $65.43 $49.17 $49.17 $49.17 $49.17
47 $22.79 $30.64 $51.35 $55.34 $59.61 $61.06 $62.92 $63.85 $63.85 $66.48 $66.48 $49.78 $49.78 $49.78 $49.78
48 $22.96 $31.25 $52.08 $56.26 $60.69 $62.15 $64.09 $65.03 $65.03 $67.33 $67.33 $50.30 $50.30 $50.30 $50.30
49 $23.49 $31.97 $52.90 $57.24 $61.87 $63.15 $65.23 $66.20 $66.20 $67.77 $67.77 $50.98 $50.98 $50.98 $50.98
50 $23.89 $32.60 $53.61 $58.16 $62.95 $64.24 $66.30 $67.28 $67.28 $67.80 $67.80 $51.57 $51.57 $51.57 $51.57
51 $24.38 $33.30 $54.45 $58.97 $64.04 $65.33 $67.73 $68.73 $68.73 $71.03 $71.03 $52.63 $52.63 $52.63 $52.63
52 $24.76 $34.02 $55.26 $59.89 $65.12 $66.42 $69.06 $70.08 $70.08 $72.29 $72.29 $53.43 $53.43 $53.43 $53.43
53 $25.24 $34.55 $56.08 $60.87 $66.28 $67.50 $70.21 $71.26 $71.26 $73.44 $73.44 $54.03 $54.03 $54.03 $54.03
54 $25.64 $35.26 $56.98 $61.70 $67.28 $68.78 $71.55 $72.61 $72.61 $74.61 $74.61 $55.03 $55.03 $55.03 $55.03
55 $26.02 $35.90 $58.08 $62.52 $68.28 $69.95 $72.61 $73.69 $73.69 $75.56 $75.56 $55.83 $55.83 $55.83 $55.83
56 $26.51 $36.60 $59.16 $63.34 $69.36 $71.23 $73.78 $74.88 $74.88 $75.57 $75.57 $56.56 $56.56 $56.56 $56.56
57 $27.01 $37.23 $60.24 $64.15 $70.36 $72.41 $75.02 $76.14 $76.14 $78.07 $78.07 $57.36 $57.36 $57.36 $57.36
58 $27.61 $37.85 $61.34 $65.05 $71.45 $73.68 $76.36 $77.49 $77.49 $79.57 $79.57 $58.39 $58.39 $58.39 $58.39
59 $27.99 $38.48 $62.42 $66.05 $72.53 $74.86 $77.71 $78.85 $78.85 $79.85 $79.85 $62.14 $62.14 $62.14 $62.14
60 $28.55 $39.09 $63.42 $66.97 $73.69 $76.12 $78.70 $79.87 $79.87 $80.07 $80.07 $62.62 $62.62 $62.62 $62.62
61 $29.05 $39.81 $64.50 $68.13 $74.88 $77.31 $80.37 $81.55 $81.55 $82.90 $82.90 $64.10 $64.10 $64.10 $64.10
62 $29.74 $40.53 $65.50 $69.41 $76.04 $78.57 $81.60 $82.82 $82.82 $85.03 $85.03 $65.15 $65.15 $65.15 $65.15
63 $30.24 $41.33 $66.60 $70.41 $77.31 $79.94 $82.95 $84.18 $84.18 $86.48 $86.48 $66.48 $66.48 $66.48 $66.48
64 $30.80 $42.12 $67.68 $71.59 $78.57 $81.30 $84.28 $85.53 $85.53 $87.88 $87.88 $67.38 $67.38 $67.38 $67.38
65 $31.30 $42.92 $68.68 $72.76 $79.84 $82.75 $85.53 $86.80 $86.80 $89.34 $89.34 $68.45 $68.45 $68.45 $68.45
66 $31.90 $43.72 $69.78 $73.94 $81.10 $84.12 $86.96 $88.24 $88.24 $90.49 $90.49 $69.51 $69.51 $69.51 $69.51
67 $32.38 $44.54 $70.78 $75.22 $82.47 $85.56 $88.46 $89.78 $89.78 $91.97 $91.97 $70.78 $70.78 $70.78 $70.78
68 $33.05 $45.34 $71.86 $76.49 $83.72 $86.83 $89.89 $91.23 $91.23 $93.44 $93.44 $71.73 $71.73 $71.73 $71.73
69 $33.65 $46.14 $72.94 $77.76 $84.98 $88.20 $91.41 $92.76 $92.76 $94.92 $94.92 $73.01 $73.01 $73.01 $73.01
70 $34.25 $46.94 $74.04 $78.94 $86.35 $89.46 $92.66 $94.02 $94.02 $96.30 $96.30 $73.03 $73.03 $73.03 $73.03
71 $34.72 $47.82 $75.04 $80.20 $87.61 $90.64 $94.34 $95.74 $95.74 $97.67 $97.67 $75.36 $75.36 $75.36 $75.36
72 $35.16 $48.63 $76.12 $81.49 $88.96 $92.01 $95.59 $97.00 $97.00 $99.23 $99.23 $76.42 $76.42 $76.42 $76.42
73 $35.73 $49.52 $77.21 $82.75 $90.23 $93.27 $96.92 $98.35 $98.35 $100.40 $100.40 $76.44 $76.44 $76.44 $76.44
74 $36.28 $50.32 $78.21 $84.02 $91.49 $94.54 $98.27 $99.72 $99.72 $101.76 $101.76 $76.46 $76.46 $76.46 $76.46
75 $36.75 $51.22 $79.30 $85.20 $92.84 $95.82 $99.60 $101.07 $101.07 $103.45 $103.45 $76.47 $76.47 $76.47 $76.47
76 $37.40 $52.01 $80.30 $86.46 $94.11 $97.09 $100.92 $102.41 $102.41 $104.81 $104.81 $80.87 $80.87 $80.87 $80.87
77 $37.96 $52.81 $81.39 $87.65 $95.29 $98.35 $102.26 $103.78 $103.78 $106.19 $106.19 $82.15 $82.15 $82.15 $82.15
78 $38.51 $53.61 $82.38 $88.74 $96.55 $99.63 $103.60 $105.13 $105.13 $107.58 $107.58 $82.17 $82.17 $82.17 $82.17
79 $39.16 $54.33 $83.48 $89.91 $97.82 $100.98 $104.94 $106.49 $106.49 $109.12 $109.12 $82.18 $82.18 $82.18 $82.18
80 $39.73 $55.13 $84.57 $91.01 $98.98 $102.16 $106.28 $107.84 $107.84 $110.61 $110.61 $82.20 $82.20 $82.20 $82.20
81 $40.21 $55.93 $85.65 $92.19 $100.25 $103.45 $107.59 $109.19 $109.19 $111.97 $111.97 $84.88 $84.88 $84.88 $84.88
82 $40.86 $56.64 $86.65 $93.37 $101.52 $104.80 $108.84 $110.46 $110.46 $114.30 $114.30 $84.90 $84.90 $84.90 $84.90
83 $41.38 $57.36 $87.75 $94.46 $102.78 $106.08 $110.27 $111.90 $111.90 $115.55 $115.55 $84.91 $84.91 $84.91 $84.91
84 $41.76 $57.98 $88.83 $95.54 $103.96 $107.34 $111.52 $113.17 $113.17 $116.73 $116.73 $84.93 $84.93 $84.93 $84.93
85 $42.22 $58.69 $89.83 $96.72 $105.23 $108.61 $112.85 $114.52 $114.52 $118.28 $118.28 $84.95 $84.95 $84.95 $84.95
86 $42.71 $59.41 $90.91 $97.82 $106.49 $109.87 $114.20 $115.88 $115.88 $119.46 $119.46 $85.93 $85.93 $85.93 $85.93
87 $43.24 $60.12 $91.91 $98.98 $107.66 $111.16 $115.52 $117.23 $117.23 $120.91 $120.91 $93.51 $93.51 $93.51 $93.51
88 $43.71 $60.84 $93.01 $100.08 $108.92 $112.42 $116.87 $118.60 $118.60 $122.39 $122.39 $94.49 $94.49 $94.49 $94.49
89 $44.21 $61.46 $94.09 $101.27 $110.19 $113.69 $118.20 $119.95 $119.95 $123.64 $123.64 $94.51 $94.51 $94.51 $94.51
90 $44.64 $62.17 $95.09 $102.35 $111.37 $114.97 $119.53 $121.30 $121.30 $125.24 $125.24 $94.52 $94.52 $94.52 $94.52
91 $45.69 $62.89 $96.19 $103.53 $112.62 $116.23 $120.88 $122.66 $122.66 $127.57 $127.57 $97.85 $97.85 $97.85 $97.85
92 $46.17 $63.59 $97.27 $104.71 $113.89 $117.50 $122.21 $124.01 $124.01 $129.04 $129.04 $97.87 $97.87 $97.87 $97.87
93 $46.60 $64.30 $98.27 $105.79 $115.15 $118.78 $123.53 $125.36 $125.36 $130.52 $130.52 $97.89 $97.89 $97.89 $97.89
94 $47.09 $64.93 $99.35 $106.98 $116.33 $120.05 $124.78 $126.62 $126.62 $131.95 $131.95 $101.71 $101.71 $101.71 $101.71
95 $47.45 $65.63 $100.45 $108.06 $117.60 $121.31 $126.21 $128.07 $128.07 $133.40 $133.40 $102.53 $102.53 $102.53 $102.53
96 $47.94 $66.27 $101.45 $109.24 $118.86 $122.68 $127.46 $129.34 $129.34 $134.77 $134.77 $104.06 $104.06 $104.06 $104.06
97 $48.37 $66.98 $102.53 $110.34 $120.13 $123.94 $128.79 $130.69 $130.69 $136.08 $136.08 $104.66 $104.66 $104.66 $104.66
98 $48.85 $67.70 $103.61 $111.52 $121.30 $125.12 $130.14 $132.05 $132.05 $137.35 $137.35 $105.81 $105.81 $105.81 $105.81
99 $49.43 $68.40 $104.61 $112.60 $122.56 $126.49 $131.45 $133.40 $133.40 $138.51 $138.51 $106.88 $106.88 $106.88 $106.88
100 $49.62 $68.93 $105.56 $113.55 $123.54 $127.54 $132.53 $134.53 $134.53 $138.53 $138.53 $106.89 $106.89 $106.89 $106.89
101 $50.11 $69.62 $106.62 $114.69 $124.78 $128.81 $133.86 $135.88 $135.88 $139.91 $139.91 $107.96 $107.96 $107.96 $107.96
102 $50.61 $70.31 $107.67 $115.82 $126.01 $130.09 $135.18 $137.22 $137.22 $141.30 $141.30 $109.03 $109.03 $109.03 $109.03
103 $51.11 $71.00 $108.73 $116.96 $127.25 $131.37 $136.51 $138.57 $138.57 $142.68 $142.68 $110.10 $110.10 $110.10 $110.10
104 $51.60 $71.69 $109.78 $118.10 $128.48 $132.64 $137.84 $139.91 $139.91 $144.07 $144.07 $111.17 $111.17 $111.17 $111.17
105 $52.10 $72.38 $110.84 $119.23 $129.72 $133.92 $139.16 $141.26 $141.26 $145.45 $145.45 $112.24 $112.24 $112.24 $112.24
106 $52.59 $73.07 $111.89 $120.37 $130.96 $135.19 $140.49 $142.60 $142.60 $146.84 $146.84 $113.31 $113.31 $113.31 $113.31
107 $53.09 $73.76 $112.95 $121.50 $132.19 $136.47 $141.81 $143.95 $143.95 $148.22 $148.22 $114.38 $114.38 $114.38 $114.38
108 $53.59 $74.45 $114.01 $122.64 $133.43 $137.74 $143.14 $145.29 $145.29 $149.61 $149.61 $115.44 $115.44 $115.44 $115.44
109 $54.08 $75.13 $115.06 $123.77 $134.66 $139.02 $144.46 $146.64 $146.64 $151.00 $151.00 $116.51 $116.51 $116.51 $116.51
110 $54.58 $75.82 $116.12 $124.91 $135.90 $140.29 $145.79 $147.99 $147.99 $152.38 $152.38 $117.58 $117.58 $117.58 $117.58
111 $55.07 $76.51 $117.17 $126.04 $137.13 $141.57 $147.11 $149.33 $149.33 $153.77 $153.77 $118.65 $118.65 $118.65 $118.65
112 $55.57 $77.20 $118.23 $127.18 $138.37 $142.84 $148.44 $150.68 $150.68 $155.15 $155.15 $119.72 $119.72 $119.72 $119.72
113 $56.07 $77.89 $119.28 $128.31 $139.60 $144.12 $149.76 $152.02 $152.02 $156.54 $156.54 $120.79 $120.79 $120.79 $120.79
114 $56.56 $78.58 $120.34 $129.45 $140.84 $145.39 $151.09 $153.37 $153.37 $157.92 $157.92 $121.86 $121.86 $121.86 $121.86
115 $57.06 $79.27 $121.40 $130.59 $142.07 $146.67 $152.41 $154.71 $154.71 $159.31 $159.31 $122.93 $122.93 $122.93 $122.93
116 $57.56 $79.96 $122.45 $131.72 $143.31 $147.95 $153.74 $156.06 $156.06 $160.69 $160.69 $124.00 $124.00 $124.00 $124.00
117 $58.05 $80.65 $123.51 $132.86 $144.55 $149.22 $155.06 $157.40 $157.40 $162.08 $162.08 $125.06 $125.06 $125.06 $125.06
118 $58.55 $81.34 $124.56 $133.99 $145.78 $150.50 $156.39 $158.75 $158.75 $163.46 $163.46 $126.13 $126.13 $126.13 $126.13
119 $59.04 $82.03 $125.62 $135.13 $147.02 $151.77 $157.72 $160.09 $160.09 $164.85 $164.85 $127.20 $127.20 $127.20 $127.20
Hi Julie,
Send me the spreadsheet at iloveyoukanwal@rediffmail.com
Kanwaljit
Julie — Just:
1. Do a MATCH() to find the right column for the weight
2. Do a MATCH() to find the right row for the zone
3. Do an OFFSET() function using the above column and row
Jesus wept!
Hi, I am looking for artwork created in Excel for a little Microsoft project. Any ideas?
Thanks!
Awesome. Did not need the Min element for what I was doing, but the use of VLOOKUP and MATCH did the trick. Thank you.