ExcelBanter - Need a formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Need a formula (https://www.excelbanter.com/excel-worksheet-functions/235078-need-formula.html)

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?

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$1 0,0))

If this post helps click Yes
---------------
Jacob Skaria

"Dragonkindred" wrote:

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?

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

"Jacob Skaria" wrote:

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$1 0,0))

If this post helps click Yes
---------------
Jacob Skaria

"Dragonkindred" wrote:

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?