Finding cell text corresponding to the MAXimum figure in a spreadsheet?

  • Thread starter Thread starter Prospero
  • Start date Start date
P

Prospero

Please can anyone help me with this... :)

I've made a spreadsheet with the following attributes:

Cells A1 to A100 contain a figure from 1-100 (a 'desirability' rating),
sorted so that the highest figures are at the top of the spreadsheet and
descend to the lowest figure in cell A100.

Cells B1 to B100 contain the title of a CD album.

Cells C1 to C100 contain the price of a CD album.

I would like to make a formula that searches for the row that produces
the highest result given from multiplying the figure in column A with
the corresponding figure in column C and returns the corresponding CD
title in column B.

Many thanks in advance,

Prospero
 
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(B1:B100,MATCH(MAX(A1:A100*C1:C100),A1:A100*C1:C100,0))
 
Back
Top