Lookups with IF, AND, Between, MIN, MAX.

O

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hello,

I've been trying to nut this out for hours now. I'm probably over complicating things.

Scenario
I have a cell that requires a value from a column in another table. This cell is called "FP Matrix"
1621823578658.png


Table - MadecoPriceGroupT

All boards price list May-2021.xlsx
RSTU
6MadeCo Price GroupsMIN PriceMAX PriceFP Matrix
7Carcass White$8.90$9.06
8Group 1$13.65$14.72
9Group 2$15.60$16.33
10Group 2a$0.00$0.00
11Group 2b$19.12$21.84
12Group 2c$22.70$26.40
13Group 2d$27.80$30.77
14Group 2e$32.61$33.72
15Group 3$35.00$37.70
16Group 4$40.91$40.91
17Group 5$43.00$44.23
18Group 6$45.99$48.07
19Group 7$51.06$51.67
20Group 8$56.65$59.86
21Group 9$64.48$65.10
22Group 10$69.80$75.17
23Group 11$80.34$84.30
24Group 12$87.55$92.29
25Group 13$100.34$100.78
ADMIN
Cell Formulas
RangeFormula
S7:S25S7=MINIFS(DataTableT[$/m2],DataTableT[Price Group],[@[MadeCo Price Groups]])
T7:T25T7=MAXIFS(DataTableT[$/m2],DataTableT[Price Group],[@[MadeCo Price Groups]])



The FP Matrix value I require to be return is in the Table - "FP_PriceFactorT" Column "ID"

1621823809284.png



All boards price list May-2021.xlsx
BINO
7B_Board Rate / M2STYLE TYPESSUPPLIERID
8$14.98# 2 - MadeCo SelectPolytec, Formica1
9$22.14# 3 - Commodity Range HMR PB & MR MDF (Plain)CHIEF, Polytec, Amerind2
10$22.13# 3 - Commodity Range HMR PB & MR MDF (W/Grain)CHIEF, Polytec, Amerind3
11$22.95# 4 - Mid Range MR MDF (Plain)Laminex4
12$22.95# 4 - Mid Range MR MDF (W/Grain)Laminex5
13# 5 - Vinyl Flat No Routing Plain (Text)Polytec 6
14# 5 - Vinyl Flat No Routing Plain (Matt)Polytec 7
15# 5 - Vinyl Flat No Routing Plain (Gloss)Polytec 8
16# 5 - Vinyl Flat No Routing W/Grain (Matt)Polytec 9
17# 5 - Vinyl Flat No Routing W/Grain (Gloss)Polytec 10
18# 5 - Vinyl Flat No Routing Metallic (Gloss)Polytec 44
19# 6 - Vinyl Basic Routing Plain (Text)Polytec 11
20# 6 - Vinyl Basic Routing Plain (Matt)Polytec 12
21# 6 - Vinyl Basic Routing Plain (Gloss)Polytec 13
22# 6 - Vinyl Basic Routing W/Grain (Matt)Polytec 14
23# 6 - Vinyl Basic Routing W/Grain (Gloss)Polytec 15
24# 7 - Vinyl Advanced Routing Plain (Text)Polytec 16
25# 7 - Vinyl Advanced Routing Plain (Matt)Polytec 17
26# 7 - Vinyl Advanced Routing Plain (Gloss)Polytec 18
27# 7 - Vinyl Advanced Routing W/Grain (Matt)Polytec 19
28# 7 - Vinyl Advanced Routing W/Grain (Gloss)Polytec 20
29# 8 - Vinyl Recessed Centre Routing Plain (Text)Polytec 21
30# 8 - Vinyl Recessed Centre Routing Plain (Matt)Polytec 22
31$26.01# 4 - Mid Range MR MDF Sheen (Plain)Polytec 23
32$30.72# 4 - Mid Range MR MDF Sheen (W/Grain)Polytec 24
33$39.14# 4 - Mid Range MR MDF (Plain)CHIEFs Panelart25
34$39.14# 4 - Mid Range MR MDF (W/Grain)CHIEFs Panelart26
35$20.97# 4 - Mid Range MR MDF (Plain)Formica27
36$20.97# 4 - Mid Range MR MDF (W/Grain)Formica28
37$14.50Style #5 Holly and None IHD Price White Satin RawMadeco29
38$14.50Style #5 Holly and None IHD Price White Satin Painted Light PigmentMadeco30
39$14.50# 2 - MadeCo SelectIn-House31
40$14.50Style #5 Holly and None IHD Price White Satin Painted Dark PigmentMadeco32
41$0.00BBT (3mm Mdf)33
42#REF!BBS (19mm flooring)34
43$47.95# 9 - 18mm Createc G1S SolidPolytec 35
44$58.20# 9 - 18mm Createc G1S TimberprintPolytec 36
45$74.93# 9 - 18mm Createc G2S SolidPolytec 37
46$84.92# 9 - 18mm Createc G2S TimberprintPolytec 38
47$47.95# 9 - 18mm Crystal Gloss G1S SolidLaminex39
48$54.95# 9 - 18mm Crystal Gloss G1S TimberprintLaminex40
49$64.94# 9 - 18mm Crystal Gloss G2S SolidLaminex41
50$74.93# 9 - 18mm Crystal Gloss G2S TimberprintLaminex42
51$25.97# 4 - Mid Range MR MDF Lamiwood Silk (Plain)Laminex43
52$25.97# 4 - Mid Range MR MDF Lamiwood Silk (W/Grains & Patterns)Laminex47
53$22.97# 4 - Mid Range MR MDF Formica Gloss (Plain)Formica48
54$22.96# 4 - Mid Range MR MDF Formica Gloss (W/Grain & Patterns)Formica49
55$8.75# 1 - 16mm White HMR TEXTURE [Carcase Board] - Matching 1mm PVC EdgePolytec50
56$8.75# 1 - 16mm White HMR MATT [Carcase Board] - Matching 1mm PVC EdgePolytec51
57Custom52
58None Required53
59$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - Raw [ready to sand & polish]Amerind54
60$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - 2 Pac Satin 30% [open grain]Amerind55
61$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - 2 Pac Semi Gloss 60% [open grain]Amerind56
62$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - 2 Pac Gloss 100% [grain filled]Amerind57
63$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - Raw [ready to sand & polish]Amerind58
64$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - 2 Pac Satin 30% [open grain]Amerind59
65$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - 2 Pac Semi Gloss 60% [open grain]Amerind60
66$0.00# 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - 2 Pac Gloss 100% [grain filled]Amerind61
67$0.00# 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - Raw [ready to sand & polish]Amerind62
68$0.00# 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - 2 Pac Satin 30% [open grain]Amerind63
69$0.00# 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - 2 Pac Semi Gloss 60% [open grain]Amerind64
70$0.00# 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - 2 Pac Gloss 100% [grain filled]Amerind65
71$18.00# 1 - 16mm Black HMR TEXTURE [Carcase Board] - Matching 1mm PVC EdgePolytec66
72$18.00# 1 - 16mm Black HMR MATT [Carcase Board] - Matching 1mm PVC EdgePolytec67
73SPARE68
74Albedor - Thermolated Door Style 5Albedor69
75Albedor - Thermolated Door Style 6Albedor70
76Albedor - Thermolated Door Style 7Albedor71
77Albedor - Thermolated Door Style 8Albedor72
78Albedor - White Satin - Raw Door Style 5Albedor73
79Albedor - White Satin - Raw Door Style 6Albedor74
80Albedor - White Satin - Raw Door Style 7Albedor75
81Albedor - White Satin - Raw Door Style 8Albedor76
82Albedor - White Satin - Paint Door Style 5Albedor77
83Albedor - White Satin - Paint Door Style 6Albedor78
84Albedor - White Satin - Paint Door Style 7Albedor79
85Albedor - White Satin - Paint Door Style 8Albedor80
86Albedor - True Reflections Door Style 5-1Albedor81
87Albedor - True Reflections Door Style 5-2Albedor82
88Albedor - Ultra Finish Door Style 5-1Albedor83
89Albedor - Ultra Finish Door Style 5-2Albedor84
90Stylelite DoorsIn House85
91$17.50# 2 - MadeCo Select Standard86
92$27.50# 2 - MadeCo Select Impressions87
ADMIN
Cell Formulas
RangeFormula
B41B41=$AH$23
B42B42=#REF!
B59,B63,B67B59=$EQ2+$EU$2
B60,B64B60=$EQ3+$EV$2
B61,B65,B69B61=$EQ4+$EW$2
B62,B66,B70B62=$EQ5+$EX$2
B68B68=$EQ12+$EV$2



Formula required in Cells FP Matrix column needs to look at the values of MIN Price and MAX Price.
Is the value in "FP_PriceFactorT[B_Board Rate/M2]" >= "MIN Price" and <= "MAX Price"
If Yes then return the value in "FP_PriceFactorT[ID]"
If no then find the value that meets criteria.

e.g.

1621824453948.png


Thank you

Chief
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Something like this maybe?? but with a lookup? or a Match? or an Index? :oops:

All boards price list May-2021.xlsx
STUVW
6MadeCo Price GroupsMIN PriceMAX PriceFP MatrixCFP MatrixNew
7Carcass White$8.90$9.06#REF!
8Group 1$13.65$14.72#REF!
9Group 2$15.60$16.33#REF!
10Group 2a$0.00$0.00#REF!
11Group 2b$19.12$21.84#REF!
12Group 2c$22.70$26.40#REF!
13Group 2d$27.80$30.77#REF!
14Group 2e$32.61$33.72#REF!
15Group 3$35.00$37.70#REF!
16Group 4$40.91$40.91#REF!
17Group 5$43.00$44.23#REF!
18Group 6$45.99$48.07#REF!
19Group 7$51.06$51.67#REF!
20Group 8$56.65$59.86#REF!
21Group 9$64.48$65.10#REF!
22Group 10$69.80$75.17#REF!
23Group 11$80.34$84.30#REF!
24Group 12$87.55$92.29#REF!
25Group 13$100.34$100.78#REF!
ADMIN
Cell Formulas
RangeFormula
T7:T25T7=MINIFS(DataTableT[$/m2],DataTableT[Price Group],[@[MadeCo Price Groups]])
U7:U25U7=MAXIFS(DataTableT[$/m2],DataTableT[Price Group],[@[MadeCo Price Groups]])
V7:V25V7=IF(AND(FP_PriceFactorT[B_Board Rate / M2]>=[@[MIN Price]],FP_PriceFactorT[B_Board Rate / M2]<=[@[MAX Price]]),FP_PriceFactorT[ID],"NOPE")
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,911
Messages
6,122,192
Members
449,072
Latest member
DW Draft

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