Forum Discussion
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 automate the pricing after entering width and drop of the product?
| Drop - Width | 1000 | 1500 | 2000 |
| 1000 | 150 | 200 | 250 |
| 1500 | 250 | 300 | 350 |
| 2000 | 350 | 400 | 450 |
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))
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?
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...
