How to make a cell return a number if in another cell I input a certain number from a range

Question

First of all, English is not my native language so explaining what I need is tricky.

So I have product that can have a range of widths. Depending on the width of the product, the aquisition price changes.

How can I make a cell where I input a width (in numbers), and depending on the range that that number is, another cell returns a certain price.

So let's say that the price for a product with widhts between 0-1000mm is 5 dollars. I want to introduce a product with a width of 500mm in a cell, and the next cell should output a price of 5 dollars.

And then let's say I want to add a product of 1300mm, and the price from 1000mm-2000mm is 10 dollars. So I would input 1300mm in a cell, and another cell should output 10 dollars.

Is that possible in excel? Please help!

Answer 1

As @AdamV said.....

1. Create a table of your price list.
   The Max column isn't needed, but helps you keep an eye on the range.
   

2. Select a cell within the table and press Ctrl+T to turn it into an Excel table.
   Note that the Table Design ribbon will appear and you can rename the table.
   

3. Use either formula:
   =XLOOKUP($H$2,Table1[Min],Table1[Price],,-1) or
   =VLOOKUP(H2,Table1,3,TRUE)
   

The -1 in XLOOKUP and TRUE in VLOOKUP is "exact match, if none found return the next smaller item".

You could also keep the data as just a range of cells, but the formula won't automatically extend as you enter new price ranges.

Edit: You could also use =INDEX(Table1[Price],MATCH(H2,Table1[Min],1))

Answer 2

New here - but wanted to add something that could be helpful. In the newer versions of excel there is an IFS function. This function was created so you don’t have to nest IF functions like have been proposed as solutions here. IFS function allow you to evaluate a series of IF functions.

I’ll give it a go now but please take reference YouTube to verify.

=IFS(A8<1000,5),(A8>1000&A8<2000,10),(IF logic #3))