# Create an array that matches 2 criteria, then select closest value in array

1. ## Create an array that matches 2 criteria, then select closest value in array

Hello. I am compiling a spreadsheet that predicts salary data for a city based on the city's cost of living. I have salary data for about 1/3 of the cities that I want, and I am missing it for the rest. The plan is to :
1) when considering all of the cities that we already have salary data for
2) only consider the ones in the same region as the city that we are calculating
3) of that group, match the closest cost of living
4) and return the salary % of that closest-match

I hope that makes sense. I have some of the formula figured out. I can't figure out how to limit my "match-the-closest" part to the proper array.

{=IF(\$J2="", INDEX(\$J\$2:\$J\$326, MATCH( INDEX(\$B\$2:\$B\$326,MATCH(MIN(ABS(\$B\$2:\$B\$326-\$B2)),ABS(\$B\$2:\$B\$326-\$B2),0)), \$B\$2:\$B\$326, 0)), \$J2)}

Edit: Getting closer... this includes the part about "if the salary info is provided, then put that in the array. But, I tried adding an AND and an If region is same," but that is not working for some reason..
Seems to work:
=IF(\$J3="", INDEX(\$J\$2:\$J\$326, MATCH( INDEX( IF(\$J\$2:\$J\$326>0, \$B\$2:\$B\$326), MATCH(MIN(ABS( IF(\$J\$2:\$J\$326>0, \$B\$2:\$B\$326) -\$B3)),ABS( IF(\$J\$2:\$J\$326>0, \$B\$2:\$B\$326) -\$B3),0)), IF(\$J\$2:\$J\$326>0, \$B\$2:\$B\$326), 0)), \$J3)

Does not work:
=IF(\$J4="", INDEX(\$J\$2:\$J\$326, MATCH( INDEX( IF(AND(\$J\$2:\$J\$326>0, \$I\$2:\$I\$326=\$I4), \$B\$2:\$B\$326), MATCH(MIN(ABS( IF(AND(\$J\$2:\$J\$326>0, \$I\$2:\$I\$326=\$I4), \$B\$2:\$B\$326) -\$B4)),ABS( IF(AND(\$J\$2:\$J\$326>0, \$I\$2:\$I\$326=\$I4), \$B\$2:\$B\$326) -\$B4),0)), IF(AND(\$J\$2:\$J\$326>0, \$I\$2:\$I\$326=\$I4), \$B\$2:\$B\$326), 0)), \$J4)

I attached the data file. It has dummy data for the salary info.

I really appreciate any help.  Register To Reply