Excel formula question

J

jymie

New Member
Joined
Mar 9, 2015
Messages
12
one column is the starting square feet, next column is the upto square feet then the price
what I want to have happen, when the result in one cell (column K Row 2) falls in between the values on a certain row (from column a to column B), the price for that row will appear in another cell (column L Row 2). I am looking for all my result to appear in cell L2


example, lets say the result in the square footage cell (column K Row 2) is 4500 sq feet, in the price cell (column L Row 2), I would want $40.00 to automatically appear, if in the same square footage cell (column K Row 2) I get a different result say its 9030, then I would want $57.00 to appear in (column L Row 2) and so on depending on the result of total square footage.

CAN THIS BE PUT ON THE SAME TAB AS THE WORKSHEET or does it have to be on a separate tab? Prefer on same tab.

price list
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]Sq Ft[/TD]
[TD="align: center"]Sq ft[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total Sq Ft[/TD]
[TD="align: center"]Price[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]5000[/TD]
[TD="align: center"]$40.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5001[/TD]
[TD="align: center"]6000[/TD]
[TD="align: center"]$44.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6001[/TD]
[TD="align: center"]7000[/TD]
[TD="align: center"]$47.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7001[/TD]
[TD="align: center"]8000[/TD]
[TD="align: center"]$50.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8001[/TD]
[TD="align: center"]9000[/TD]
[TD="align: center"]$53.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9001[/TD]
[TD="align: center"]10000[/TD]
[TD="align: center"]$57.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

A B C J K
Square Ft Price
Sq Ft Sq ft Cost xxx yyy
0 5000 $40.00
5001 6000 $44.00
6001 7000 $47.00
7001 8000 $50.00
8001 9000 $53.00
9001 10000 $57.00
10001 11000 $60.00
11001 12000 $63.00
12001 13000 $66.00
13001 14000 $69.00
14001 15000 $72.00
15001 16000 $75.00
16001 17000 $78.00
17001 18000 $81.00
18001 19000 $84.00
19001 20000 $87.00
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
With your sample data in the range A2:C17 (adjust to suit) and your reference / lookup square footage in K2, this formula (in L2) should work (array formula so use Ctrl+Shift+Enter)

Code: 
=INDEX($A$2:$C$17,MATCH(1,($K$2>=$A$2:$A$17)*($K$2<=$B$2:$B$17),0),3)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel formula/syntax question
Replies
4
Views
451
mkrass
M
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
398
XLfriend777
XLfriend777
Saher Naji
  • Question Question
MAO
Replies
1
Views
492
igold
igold
A
  • Solved
transpose data for each sheet based on multiple prices
2
Replies
10
Views
349
abdo meghari
A
T
  • Question Question
Counting cells in a column in an array based on criteria
Replies
0
Views
897
tjdickinson
T

Forum statistics

Threads
1,222,790
Messages
6,168,259
Members
452,174
Latest member
bhorsley89

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top