'find a walue in a sheet

New Contributor

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

5 Replies

@Christina1970 

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

@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-6061-8081-100
1000-1999230022002100
2000-2099220021502100
2100-2199210020001900
2200-2299200019001800
2300-2499190018001700
2500-2599180017001600
2600-2890175016501550
2900210020001900
2901-2942190018001700
2950-2973175016501550

@Christina1970 

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

image.png

Hi @Sergei Baklan 

 

It is correct that the postcode 2900 is standing alone

 

//Christina

@Christina1970 

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

image.png

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")