Forum Discussion

alxarutiunov's avatar
alxarutiunov
Copper Contributor
Sep 19, 2022
Solved

Vlookup for a price list based on width X height in a table

Hi Everyone   Can you please help me with the following issue. We have price lists in the below format that we use to price up our products. Is it possible to create a spreadsheet that would automa...
Formulas and Functions
mathetes's avatar
mathetes
Silver Contributor
Sep 19, 2022

alxarutiunov 

 

See the attached file.

I'm sure your actual contains more options, but a formula following this model will work.

=INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0))

  • alxarutiunov's avatar
    alxarutiunov
    Copper Contributor
    Sep 20, 2022

    mathetes 

    Thanks! Sorry, should have explained better.

     

    Can this be amended so that I can enter width/drop manually and not simply selected from the drop down lists?

     

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Sep 20, 2022

      alxarutiunov 

      Simply select H2:H3, click Data Validation on the Data tab of the ribbon, and click OK when prompted to remove data validation, then click OK again. But you'll get #N/A if you enter a value different from 1000, 1500 and 2000...

      • alxarutiunov's avatar
        alxarutiunov
        Copper Contributor
        Sep 20, 2022

        HansVogelaar 

         

         

         

        How do I make it work in a way so that I can use any number and not get an error?

         

        I need to be able to price a product of any size and the table to be able to understand which size bracket (both width and drop) it falls into and produce a correct answer.

Resources