Looking for Value in Another Column, Evaluating and Then Looking in Another Column

[Rick Rothstein]

I'm half asleep, but, does this formula do what the original did? If I paste it below the 1st time will it give me the 2nd best, or highest, value?....

Remember I said "The first instance of this UDF must be placed on the same row as the first (topmost) number in your two columns of numbers"... the reason is the formula needs a starting point to calculate from, so I used the top row of the two columns of numbers. From there, you can copy the formula down as far as you like. So, the highest value will be on the same row as the top cell of the two columns of numbers, the second highest will be under that one and so on.

[olives]

... Does this modification do what you want?

Ok, I don't know if I'm doing anything wrong, but I set up the function and when I have it read =Largest(D$1, P$1) it does give me the highest value in Column Q but if I change the value in D$1, I'm getting the #VALUE error. I can't tell if it's working ...

I also don't get any values below where I insert the function if I copy it below the first instance. I get the #VALUE error again.

Would you like me to set up and upload a dummy data sheet?...

And, I don't know anything about the functions, but, to make it even more 'portable', I know you have the function default to two contiguous columns, but also be able to have it look something ike this: ?


=Largest(F$2, C$3, G$3)

Meaning:

1. Compare the value in Cell F$2 with all the values in Column C, then for all the values in Column C that are below the value in F$2, go to the same row in Column G, compare those values and give me the highest, etc...?

Where I could designate those columns? Just wondering... I gotta learn how to program! Thanks!

[olives]

... I said "The first instance of this UDF must be placed on the same row as the first (topmost) number in your two columns of numbers"... the reason is the formula needs a starting point to calculate from, so I used the top row of the two columns of numbers. From there, you can copy the formula down as far as you like. So, the highest value will be on the same row as the top cell of the two columns of numbers, the second highest will be under that one and so on.

I hadn't read the above before sending out my last message, but yes, I have them lined up as you have described.

[Rick Rothstein]

Would you like me to set up and upload a dummy data sheet?...

Yes, please.

[olives]

Ok, Rick, I set up the example. The formula is working fine in it. I guess I did something wrong when I set it all up before. I'll upload an example later tonight since the example I have right now is too big to upload. Thanks, Rick!

[olives]

Ok, here we go. Sorry this took so long, here's the example with questions:

book 1.xlsm


After the first setup I guess I can drag the "Results" cells anywhere, right? Thanks, Rick!

[olives]

... Sorry this took so long...

Rick, I guess my main question is, can I designate the 2nd column, instead of relying on the fact that, as they stand, the columns I'm looking at, are contiguous? Thanks!

[olives]

Rick, how can I change your code so I have more flexibility with the placement of the columns? Right now it works fine, but if I wanted to put several columns of data between the two columns I'm evaluating, I'd probably lose the functionality of your code as it is written, right?... Thanks!

[olives]

Rick, how can I change your code so I have more flexibility with the placement of the columns?

I've been trying to figure this one out...

[Rick Rothstein]

I've been trying to figure this one out...

Sorry about my absence from this forum. I just wanted to check if you were still following this thread before I attempted to modify my code in resposnse to the questions you asked inside of your posted workbook.