+ Reply to Thread
Results 1 to 1 of 1

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

  1. #1
    Registered User
    Join Date
    Atlanta, GA
    MS-Off Ver
    Excel 2007

    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.
    Attached Files Attached Files
    Last edited by jrm0523; 10-13-2012 at 10:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1