Expanding on the INDEX function
 

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.

Expanding on the INDEX function
 

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.

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.