'find a walue in a sheet

[Christina1970]

Hi all

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?

A5:A25 is the postcode interval and B5:G5 is the Sqm interval

I put in the postcode in A1 and the Sqm in B1 and need the price in E1

Hope there is one in here that can assist me in this.

Christina1970

[#2]

@Christina1970 

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

[#3]

@Sergei Baklan 

Hi Sergei

I 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 2200

Does that make sence? 

	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

[#4]

@Christina1970 

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

[#5]

Hi @Sergei Baklan 

It is correct that the postcode 2900 is standing alone

//Christina

[#6]

@Christina1970 

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>=--LEFT($B$6:$B$15,4))*($A$2<=--RIGHT($B$6:$B$15,4)),0),
          0),
     0)),
"no price")