Forum Discussion
Formula help multiple value lookup
I have the data below. I want to create a sort of calculator where the user will choose a manufacturer, panel amount, and method of payment and it will return the price. For example, they choose Trin...
Try this:
- Organize Your Data Table
Let’s assume your pricing table looks like this:
|
Manufacturer |
Panel Min |
Panel Max |
Cash Price |
SAC Price |
|
TRINA |
16 |
20 |
3.25 |
3.62 |
|
TRINA |
21 |
29 |
3.10 |
3.45 |
|
TRINA |
30 |
999 |
3.06 |
3.40 |
|
QCELL |
16 |
20 |
3.45 |
3.83 |
|
QCELL |
21 |
29 |
3.30 |
3.62 |
|
QCELL |
30 |
999 |
3.26 |
3.62 |
Let’s say this table is in cells A2:E7.
- Create Input Cells
- Manufacturer: G2
- Panel Count: G3
- Payment Method (Cash or SAC): G4
- Use a Formula to Return the Price
=INDEX(
E2:E7,
MATCH(1,
(A2:A7=G2) *
(G3>=B2:B7) *
(G3<=C2:C7),
0)
)This formula assumes G4 is set to "SAC" and returns the SAC price. If you want to switch between Cash and SAC dynamically, use this:
=INDEX(
IF(G4="Cash", D2:D7, E2:E7),
MATCH(1,
(A2:A7=G2) *
(G3>=B2:B7) *
(G3<=C2:C7),
0)
)