'find a walue in a sheet

Question

Hi all&nbsp;I have a file with postcodes in interval in column and interval of Sqm in row.The price per Sqm is to be found in the 2 intervals. How du I set up the formulas to automatic give me the price after entering the postcode and the sqm?&nbsp;A5:A25 is the postcode interval and B5:G5 is the Sqm intervalI put in the postcode in A1 and the Sqm in B1 and need the price in E1&nbsp;Hope there is one in here that can assist me in this.&nbsp;Christina1970

sergeibaklan · Answer

Christina1970&nbsp;
It's not clear where is price per sqm. Could you please provide small sample file to illustrate the question?

christina1970 · Answer

SergeiBaklan&nbsp;Hi SergeiI have provided a smal sample below. the price per sqm with the postcode 1850 and sqm 58 wil be the price in B6 but if the sqm is 70 the price is in C6 and is 2200Does that make sence?&nbsp;&nbsp;&nbsp;0-6061-8081-1001000-19992300220021002000-20992200215021002100-21992100200019002200-22992000190018002300-24991900180017002500-25991800170016002600-289017501650155029002100200019002901-29421900180017002950-2973175016501550

sergeibaklan · Answer

Christina1970&nbsp;
Thank you. 2900 without the range is that misprint or it shall be as it is?

christina1970 · Answer

Hi&nbsp;SergeiBaklan&nbsp;&nbsp;It is correct that the postcode 2900 is standing alone&nbsp;//Christina

sergeibaklan · Answer

Christina1970&nbsp;
I'd suggest to use only left boundary for sqm or at least add helper row with it

with that formula for the price could be
=IFNA(
  LOOKUP($B$2, $C$5:$E$5,
    INDEX($C$6:$E$15,
          MATCH(1,INDEX( ($A$2&gt;=--LEFT($B$6:$B$15,4))*($A$2&lt;=--RIGHT($B$6:$B$15,4)),0),
          0),
     0)),
"no price")

Forum Discussion

'find a walue in a sheet

5 Replies

Resources

	0-60	61-80	81-100
1000-1999	2300	2200	2100
2000-2099	2200	2150	2100
2100-2199	2100	2000	1900
2200-2299	2000	1900	1800
2300-2499	1900	1800	1700
2500-2599	1800	1700	1600
2600-2890	1750	1650	1550
2900	2100	2000	1900
2901-2942	1900	1800	1700
2950-2973	1750	1650	1550