Expanding on the INDEX function
The results I am looking for are the matching text in column A based on what
data in column Q matches data in column N.
- I have data in column N
- I have that same data sorted in column Q using =LARGE($N$12:$N$43,1)
- I want the results in column Q to find their match in column N then have
the corresponding data in column A from that row to show in column S.
The INDEX formula works fine, but if there are duplicate results in column Q
its search in column N always stops at the first instance it matches. This
results in multiple duplicates from column A, when in fact, no duplicate data
from column A should appear.
I think I explained it better, here. :-)
"Barb Reinhardt" wrote:
I'm thinking you'd need to use the COUNTIF function in some way. Without
seeing more data, I can't help.
Try putting this in an adjacent column to see what it gets you.
=COUNTIF(Q12,Q$12:Q12)
--
HTH,
Barb Reinhardt
"NM" wrote:
Im using the following formula to and am wondering if it can be used more
efficiently, or if I need something completely different.
=INDEX($A$12:$A$43,MATCH(Q12,$N$12:$N$43,0))
Data from column N is sorted from highest to lowest into column Q in my
Excel spreadsheet.
I have duplicates in column Q, so the above formula Im using isnt working
like I need it to. I need the formula to continue to the next occurrence in
column N when a duplicate is encountered and place that result from column A
into column S. I need this to continue as such as need be. I have over ten
duplicate numbers in column Q, but those duplicate numbers represent ten
different items.
For example I have this data:
1 DMWF
1 DMWF
1 DMWF
0 CC
0 CC
0 CC
However, it should look like this:
1 DMWF
1 HD
1 P (LC)
0 CC
0 LTC
0 HTOAC
Any help would be appreciated.
Thank you.
|