Lookup Value

[Greg]

Hi all

I am wanting a formula to do 2 thing for the one cell

I need it to look up in column "f" on a page called current the highest
number in this column

I know to use the max formula but I then need it to show the corresponding
rows item in column b

I am not sure how to do this?

Thanks

Greg

 

[JakeyC]

If you are looking up the max value from column F on sheet 'current'
and want to return the value of the corresponding cell in column B of
the same sheet, use

=INDIRECT("current!"&(ADDRESS(MATCH(MAX(current!F:F),current!F:F,0),2)))

on a separate sheet in the same workbook as 'current'. You can change
the number at the end of the formula to be whichever column you want to
return - eg. for column B instead of D, change the 2 to 4.

The F:F reference assumes you're using the entire column.

This might not be the neatest way to do it, but it worked for me!