Need a formula

D

Dragonkindred

I need a formula which will return information from a cell which is not part
of the formula. Confused? I know I am.

Excerpt from table which is much, much larger.

NBMS codes Jul-08 Aug-08 Sep-08 Q1
20HR 0
21PC 1 1
22PR 0
A101 0

Table to create (which will then get a graph)

Top NBMS Code
1
2
3

Now, the LARGE formula will give me the top totals but how do I get the
other information such as the corresponding code?
 
J

Jacob Skaria

If I understand correctly INDEX() MATCH() combination or a LOOKUP() will do.
Suppose you have the below table in Sheet1. Try the below formula in B2 in
place of xxx

Top NBMS Code
1 xxx
2 xxx
3 xxx

Assuming your larger table is in Sheet2 ColA with NBMS codes in ColA and the
lookup value (in this case 1) in colB

=INDEX(Sheet2!$A$1:$A$10,MATCH(A1,Sheet2!$B$1:$B$10,0))

If this post helps click Yes
 
D

Dragonkindred

Hi

Thank you so much Jacob. This does work except if there is a value which is
the same. Because I am looking at the top 10 values sometimes I can get
identical values. The formula works but, if there are two 2s, it only gives
me the NBMS code of the first one for both. Any ideas?

V
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Dates in a range based on Column Header Id without using sum-product 1
help with date formula 5
When building a chart Dates are been included that are not in thedate range 2
TODAY() function problem 5
how to create a formula with expiry condition 2
Counting the # of values in a range ??? 8
If statements?? 2
Plotting Graph with dynamic table 3

Top