finding the cell position depending on the value?

[eHanSolo]

hi all,

if i were to use the functions; max, min, large or small... is there a way to get the cells position?


any help would be greatly appreciated!

thanks!

e

 

[xlbo]

have a look at the MATCH function

 

[eHanSolo]

thanks for that...

i'm getting the row value of 124 when i combine it with the large like this:

=MATCH(LARGE(J1:J183,1),J1:J183,0)

I then want to use the offset function but this needs a reference.... the column is fixed and i have the row val from the match function from before... but i'm not sure how to put it all together!

plz help!

e

 

[xlbo]

if you have a row value and a column number, to get a reference, use the ADDRESS function

alternatively, if you have a column letter, you may use the INDIRECT function

 

[xlbo]

alternatively, you can just use OFFSET:

the syntax for offset (from the help files) is:

=offset(start reference, rows, columns, height, width)

you should be able to infer from what you have done with the MATCH function is to determine how many rows down you are from a start point - that can therefore go into the "rows" part of the function. All you then have to do is enter the number of columsn across you want to go and Bob is your mother's brother

=offset(J1,MATCH(LARGE(J1:J183,1),J1:J183,0),colsAcross,1,1)

Rather than just asking for generic help, please be prepared to try things out and make use of the help files.....