New Contributor

# '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 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

# Re: 'find a walue in a sheet

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

# Re: 'find a walue in a sheet

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

# Re: 'find a walue in a sheet

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

# Re: 'find a walue in a sheet

It is correct that the postcode 2900 is standing alone

//Christina

# Re: 'find a walue in a sheet

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