Jan 20 2021 01:39 AM
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
Jan 20 2021 01:48 AM
It's not clear where is price per sqm. Could you please provide small sample file to illustrate the question?
Jan 20 2021 02:01 AM
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 |
Jan 20 2021 02:24 AM
Jan 20 2021 02:32 AM
Jan 20 2021 02:51 AM
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")