Finding the closest value within a list

[4jeffthornton]

Can anyone help me with a formula to find the closest property to the subject?

I have tried the advanced filter, but the results were a range and not the closest the subject.

I need a minimum of 5 properties but more is better.

The subject criteria is: NEIG=1101, STYLE=RANCH, GRADE=B- AND TOT SQFT=2100

The STYLE must be absolute, but the NEIG, GRADE and SQFT can vary but prefer to match the subject.

NEIG STYLE GRADE CDU YEAR BUILT TOT SQFT

1098 RANCH C+ 5 1961 1805
1099 RANCH A- 6.6 1988 2996
1099 CONDO B 6.4 1970 2942
1099 RANCH B- 6.4 1955 2752
1099 RANCH B+ 6 1968 2461
1100 RANCH A 7.2 1969 5015
1100 RANCH A- 7.6 1951 3431
1100 CONDO A+ 7.8 1968 3131
1101 RANCH B 6.8 1959 1798
1101 RANCH B- 5.4 1954 1981
1101 RANCH B- 5.2 1960 1883
1101 RANCH B- 6.4 1963 2493
1101 CONDO B- 6 1964 2295
1101 RANCH B- 6 1964 2646
1101 CONDO C 5.4 1955 1691
1101 RANCH C 4.1 1961 1743
1101 RANCH C+ 6 1955 1662
1101 RANCH C+ 5.2 1952 1459


Thank You,

Jeff

If anyone needs the actual excel sheet I would be happy to sends a copy.

 

[4jeffthornton]

Hi jumbledore

I am unable to access those sheets from my location, so I can't check what you have done, but the OP will still need to decide on a weighting for 'closeness' if a formula is to be constructed.

Mackers

This is a actual excel work book with the sheets
1. 1st sheet named subject is the criteria that I need to find the closest sale to.
2. 2nd sheet is names MARKETGRID is my program not given. (blank)
3. 3rd is named SALES 2012-2014; these are the actual sales I need to extract the 5 comparable or (sales needed for the market grid).

here is a copy of the worksheet link below

http://www.filedropper.com/copyofbookxxxfromhelpfrommrexcel

Thank You very much for helping me out. I really appreciate it.

Jeff

 

[4jeffthornton]

This is jeff asking for help. I replied to my own question because I have the actual work sheet in http://www.filedropper.com/copyofbookxxxfromhelpfrommrexcel


This is a actual excel work book with the sheets
1. 1st sheet named subject is the criteria that I need to find the closest sale to.
2. 2nd sheet is names MARKETGRID is my program not given. (blank)
3. 3rd is named SALES 2012-2014; these are the actual sales I need to extract the 5 comparable or (sales needed for the market grid).

here is a copy of the worksheet link belowhttp://www.filedropper.com/copyofbookxxxfromhelpfrommrexcel

Thank You very much for helping me out. I really appreciate it.

Jeff

 

[4jeffthornton]

Sorry the first filedrop.com was the wrong file.

This is a actual excel work book with the sheets
1. 1st sheet named subject is the criteria that I need to find the closest sale to.
2. 2nd sheet is names MARKETGRID is my program not given. (blank)
3. 3rd is named SALES 2012-2014; these are the actual sales I need to extract the 5 comparable or (sales needed for the market grid).

Here is a copy of the worksheet link below

This is the correct file !!!
http://www.filedropper.com/help07-02-2015mrexcelforum

Thank You very much for helping me out. I really appreciate it.

Jeff

 

[4jeffthornton]

Sorry, the first filedrop.com was the wrong file.

the correct file is:

http://www.filedropper.com/help07-02-2015mrexcelforum