Finding where an item occurs in a list

knpaddac

New Member
Joined
Feb 11, 2014
Messages
33
I have a list of values. These particular values are numbered 1 to 90 (in A2:A91). The values in column B (B2:B91) are 1 to 45 with each occurring twice.

I want to setup formulas on a different location on the sheet so that I can have a list of the where each occurrence of the values occurs in the list... For example, if I were looking for the second occurrence of value 27 in column B a formula that would return number 55 from column A....assuming that that is the corresponding location related to that value.
Make sense? Is it possible?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
How about


Book1
ABCDEFGHI
1FindOccurance
2113211
322
433
544
655
766
877
988
1091
11102
12113
13124
14135
15146
16157
17168
181710
19189
20197
212010
22219
23227
242311
252411
Request
Cell Formulas
RangeFormula
I2=INDEX(A2:A25,AGGREGATE(15,6,(ROW(A2:A25)-ROW(A2)+1)/(B2:B25=G2),H2))
 
Upvote 0

Forum statistics

Threads
1,215,428
Messages
6,124,832
Members
449,190
Latest member
rscraig11

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