look up using duplicate look up values with multiple results

rgs694

New Member
Joined
Sep 18, 2013
Messages
28
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.

A1B1C1D1E1
14
25%1425%
1111%1420%
145%145%
1010%1215%
1420%126%
126%1111%
1215%1010%
95%95%
83%83%

<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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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

New Member
Joined
Sep 18, 2013
Messages
28
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top