Forum Discussion

Christina1970's avatar
Christina1970
Copper Contributor
Jan 20, 2021

'find a walue in a sheet

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 p...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 20, 2021

Christina1970 

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

Christina1970's avatar
Christina1970
Copper Contributor
Jan 20, 2021

SergeiBaklan 

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
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 20, 2021

    Christina1970 

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

    • Christina1970's avatar
      Christina1970
      Copper Contributor
      Jan 20, 2021

      Hi SergeiBaklan 

       

      It is correct that the postcode 2900 is standing alone

       

      //Christina

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 20, 2021

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

Resources