large function result as reference for offset function

[Z]

Dears,

I used to use LARGE function to bring the 5 largest deals from another
worksheet (database, same workbook), but I want to bring also the related
cells (same row), so I would be able to show, for instance, company, close,
date and amount.
How can I extract the cell name reference to retrieve it in another cell?

 

[RagDyer]

You could use the Index/Match combination.

Col A = Cust
Col B = Addr
Col C = Dollar Amt.
Col D = Date
Col E = SalesPerson

Row1 = Headers

For Cust name:
=INDEX(Sheet2!A2:E100,MATCH(LARGE(Sheet2!C2:C100,1),Sheet2!C2:C100,0),1)
Last "1" in the formula is the column number of datalist to return,

SO - Date would be:
=INDEX(Sheet2!A2:E100,MATCH(LARGE(Sheet2!C2:C100,1),Sheet2!C2:C100,0),4)

AND - SalesPerson would be:
=INDEX(Sheet2!A2:E100,MATCH(LARGE(Sheet2!C2:C100,1),Sheet2!C2:C100,0),5)

Amount is therefore:
=LARGE(Sheet2!C2:C100,1)

You would of course change the Large() formula to return the 2nd, 3rd, etc.
largest.

Since you say that you've used Large() before, you're familiar with what
happens with duplicates.