Can Excel Index-Match or something like it match 1 row but return another?

1
votes

I have a chart that shows the salaries for various medical specialties by percentile (each row is a specialty and each column is a percentile).

I want to be able to take a separate chart of physicians and their salaries, and automatically fill in what percentile each of their pay is.

https://i.imgur.com/NCtHHAB.jpg

If the physicians were all in one specialty, then I could just use Index-Match like so: 

=INDEX('[Percentiles Chart]'!$B$3:$F$4, 1, MATCH(K3,'[Percentile File]'!$B$4:$F$4,1))

This formula works because it's choosing the column based on just the salaries in that one row, and always returning the top row. If I wanted it to choose between different rows, how could I do that without writing an "If" formula for every single row? In other words, I want excel to look down column A in the example picture to pick the right specialty, then compare the salaries across the row and return what's in row 2.

Thank you!

1
arraysexcelindexingmatch
This would be a lot easier to track with some sample data. Can you edit your question to provide some, as well as the expected outcome? What criteria do you use you choose the row you want is not clear to me – cybernetic.nomad
Index can take a row and column parameter, each can be a Match lookup – chris neilsen
What do you mean by "choose between different rows"?. Having a sample data will help to give you a better answer. – Gangula
I added a link to a photo with example data. It won't let me embed the photo directly until I have more points on here I guess. – JacquelynK

1 Answers

1
votes

As you rightly mentioned, there is no easy way to accomplish what you are looking for since the combination of Index/Offset/Match functions will return the data from the row that is matched and not some other row. Here is a formula that I have tried and it works exactly like you want. It is slightly hacky and uses SUMPRODUCT but gets the job done.

In your spreadsheet, if you enter the below formula, you would see that it gets the correct percentile value.

=OFFSET($B$2,0,SUMPRODUCT(($A$3:$A$6=J3)*($B$3:$F$6<K3)),1,1)

Here is how it works. The first part Sumproduct basically matches the name of specialty in range A3 to A6 and returns true for a match. The second part in sumproduct checks how many values in the range B3 to F6 are less than the given value (for example K3). When these two results are multiplied, it gives the number of values less than given value in the row of matching specialty which is equivalent to the offset of the column in the range. So, the OFFSET function simply navigates to that column and returns the value of the percentile. The below screenshot should help you understand it better.

If my explanation confused you :), you may want to play around with this formula to see how exactly it works.

Note the last column that says 100 in the percentiles table. That is simply to show correct value in case salary is greater than 90 percentile value.

enter image description here