cell reference: column fixed and row the output of function

[creditman]

How do I reference a cell with the column fixed and the row an output of a function? Thanks!

 

[Zack Barresse]

Hi, welcome to the board!

Is this formula or VBA? Can you describe to us what it is you are trying to do, and what you have to work with?

 

[creditman]

I am working with formulas. I want to subtract the value of a cell who's location is Column: E Row: ROW(MAX(D2:D100))

 

[PaddyD]

review the excel help file entries for index(), match(), indirect() and offest(). post back if you can't get it sorted.

 

[creditman]

ADVERTISEMENT

Thank you, but I found another problem. I want the output on MAX($D$2:$D$100) to be the cell that the maximum value is in. How do I do this? Thank you in advance!

 

[barry houdini]

I am working with formulas. I want to subtract the value of a cell who's location is Column: E Row: ROW(MAX(D2:D100))

=LOOKUP(2,1/(D2:D100=MAX(D2:D100)),E2:E100)

Note: if there is a tie for MAX(D2:D100) the above formula will give the value in column E corresponding to the last match in D

 

[creditman]

Thank you all, I fixed the problem with the MATCH and INDEX functions

 

[barry houdini]

Yeah, I was probably making it too complex above, couldn't you simply use VLOOKUP?

=VLOOKUP(MAX(D2:D100),D2:E100,2,0)