I have a spreadsheet that vba populates for me. The columns are always in descending order. I need to find the array in column AL where the value of the cells are 1. I then need to use that array to find the values in column AV for the corresponding row #s from the first array. Once I have the second array, I find the max value. Whatever row the max value is on, I need to get the number from the corresponding row in column AS.

For Example: 1. Column AL - The array where the cell values are 1 would be AL36:AL42
2. From the row numbers of the first array (AL36:AL42) i need to find the values of cells in column AV which would be in array AV36:AV42
3. I need the highest value in the second array which would be cell AV36
4. I then need to get the value of row 36 for column AS, which would be 9
5. I then need to populate that number (9) in cell B4.


I hope I have explained this well enough.

I am attaching a spreadsheet as well.

Thanks in advance!!

there is no attachment

please make sure to ZIP and then upload.
what version of excel are you using.

Sorry about that.

It's 2007.

This sounds like a job for an array formula. The answer I came up with was:

=OFFSET(AS3,MATCH(MAX(IF(AL3:AL100=1,AV3:AV100,FALSE)),AV3:AV100,0)-1,0)

entered as an array formula. The bolded "1" is what you specified. I assume you would want to read this value from a cell so you can specify some other number. I would also recommend replacing the xx3:xx100 with named dynamic ranges unless you are certain that you won't exceed Row 100 on the data.