look up using duplicate look up values with multiple results

[rgs694]

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.

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]25%[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11%[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]5%[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10%[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20%[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]6%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]6%[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]11%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]15%[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5%[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3%[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]3%[/TD]
[/TR]
</tbody>[/TABLE]


<--- 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

 

[circledchicken]

Hi,

One possible option might be:

-> Add headers to your data in columns A and B (e.g Category and Outcome)
-> Select all the data in columns A and B and create a PivotTable
-> Drag the Category field into the row labels area
-> Drag the Outcome field into the row labels area as well (below the Category field)
-> In the PivotTable Design tab, change the Report Layout to Tabular form and remove Subtotals and Grand Totals
-> Sort both fields in Descending order

 

[rgs694]

Hi,
Just getting to this now, but you're right. The Pivot Table is the way to go, been meaning to use it more. Thank you! -Rich

 

[circledchicken]

You're welcome - thanks for the feedback.