How can I use both RANK and MATCH
 

Hi, I have a data table with say 12 columns (A-M) and 12 rows (1-12) of data.

I have create a formula as follows to extract one number depending on my
search criteria.
OFFSET('DataTable'!$A$3,MATCH($A$3,' DataTable '!$A$4:$A$12,0),MATCH(C11,'
DataTable '!$B$2:$FE$2,0))

My question is, can I modify this formula to give the rank of a certain
column. Ie in column D is the third number down RANK 1 in terms of greatest
value?

RANK('52 L'!D3,'52 L'!D1:M12)

Many thanks for any advice.
Andy

How can I use both RANK and MATCH
 

Would you like to (a) explain the first formula in more detail - its
purpose, and (b) give more detail on the last question.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Andy the yeti" wrote in message
...
Hi, I have a data table with say 12 columns (A-M) and 12 rows (1-12) of
data.

I have create a formula as follows to extract one number depending on my
search criteria.
OFFSET('DataTable'!$A$3,MATCH($A$3,' DataTable '!$A$4:$A$12,0),MATCH(C11,'
DataTable '!$B$2:$FE$2,0))

My question is, can I modify this formula to give the rank of a certain
column. Ie in column D is the third number down RANK 1 in terms of
greatest
value?

RANK('52 L'!D3,'52 L'!D1:M12)

Many thanks for any advice.
Andy

How can I use both RANK and MATCH
 

Hi,

The first formula works as a double lookup returning a single value based on
two search criteria, I would ideally like to develop this to search for a
column of data within the table and then apply a =RANK formula to it.

Hope this helps and sorry for the confusion.


"Bernard Liengme" wrote:

Would you like to (a) explain the first formula in more detail - its
purpose, and (b) give more detail on the last question.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Andy the yeti" wrote in message
...
Hi, I have a data table with say 12 columns (A-M) and 12 rows (1-12) of
data.

I have create a formula as follows to extract one number depending on my
search criteria.
OFFSET('DataTable'!$A$3,MATCH($A$3,' DataTable '!$A$4:$A$12,0),MATCH(C11,'
DataTable '!$B$2:$FE$2,0))

My question is, can I modify this formula to give the rank of a certain
column. Ie in column D is the third number down RANK 1 in terms of
greatest
value?

RANK('52 L'!D3,'52 L'!D1:M12)

Many thanks for any advice.
Andy