I'm an Excel 2010 user, and have been searching the web and other posts for a while trying to figure this one out. I can find similar posts and instructions that touch upon nested Index/Match formulas, but none of them seem to address this particular arrangement of data.
What I'm trying to do is look up a value in a two column data array, and return a value from this array. The challenge is that I have duplicate look up values existing in the first column, but have different return values in the second column. In D1, I have the values sorted in descending order (including duplicates), and would like to enter a formula in E1 that would return the corresponding values in B1. Although there are duplicate look up values in D1, the data I'm trying to extract from B1 is unique for each occurrence of the duplicates.
<tbody>
</tbody>
<--- What formula to enter in E1 to return corresponding values in B1 ?
Is there a way to configure a nested function to accommodate the multiple look up values in A1, return the different results from B1, all while accommodating how the data is presented in column D1 (ie, '14' listed three times)?
The example above is somewhat simplified. My actual data table is much bigger, and I'll be using the largest 10 values to enter into D1. Otherwise, I would simply work with the A1-B1 array by itself.
Thank you much,
-Rich
What I'm trying to do is look up a value in a two column data array, and return a value from this array. The challenge is that I have duplicate look up values existing in the first column, but have different return values in the second column. In D1, I have the values sorted in descending order (including duplicates), and would like to enter a formula in E1 that would return the corresponding values in B1. Although there are duplicate look up values in D1, the data I'm trying to extract from B1 is unique for each occurrence of the duplicates.
| A1 | B1 | C1 | D1 | E1 |
| 14 | 25% | 14 | 25% | |
| 11 | 11% | 14 | 20% | |
| 14 | 5% | 14 | 5% | |
| 10 | 10% | 12 | 15% | |
| 14 | 20% | 12 | 6% | |
| 12 | 6% | 11 | 11% | |
| 12 | 15% | 10 | 10% | |
| 9 | 5% | 9 | 5% | |
| 8 | 3% | 8 | 3% |
<tbody>
</tbody>
<--- What formula to enter in E1 to return corresponding values in B1 ?
Is there a way to configure a nested function to accommodate the multiple look up values in A1, return the different results from B1, all while accommodating how the data is presented in column D1 (ie, '14' listed three times)?
The example above is somewhat simplified. My actual data table is much bigger, and I'll be using the largest 10 values to enter into D1. Otherwise, I would simply work with the A1-B1 array by itself.
Thank you much,
-Rich
