Forum Discussion

sbennett2345's avatar
sbennett2345
Copper Contributor
Sep 29, 2025

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 Trina, 22 panels, and cash and they get a result of $3.10. Can anyone help me figure out how to do that?

 

 

excel
Formulas and Functions

1 Reply

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Oct 03, 2025

    Try this:

     

    1. 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.

    1. Create Input Cells

     

    • Manufacturer: G2
    • Panel Count: G3
    • Payment Method (Cash or SAC): G4
    1. 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)
)

     

Resources