Lookup Date Help

[joferder]

I have a spreadsheet that I compiles the earliest and latest date that it snows each since 1988. With help from people on this forum I have been able to get it so that it will ealiest date and latest date by converting it from DD-MMM-YY format to just MMDD. The problem that I am having now is that I would like to use a simple Lookup formula to look with in the column to find that number and then return the Date that is in the cell just to the left. I know that it usually is the reverse, but this is how it is layed out. I have the formula (at least I think this is supposed to be how it works) and I keep getting an error. The first formula is:

=LOOKUP(S2,S6:S490,R6:R490)

In cell "S2" the formula is =MIN(S6:S490) and it comes up with a number of 1009, which I can see is in cell "S267", but instead of giving me the data in cell "R267" (9-Oct-09) it just comes up with "#N/A". I have tried to reformat the cells in every which way I can think of and it doesn't make any difference.

The second formula that I am having a problem with is:

=LOOKUP(U2,U6:U490,T6:T490)

In cell "V2" the formula is "=MAX(U6:U490) and it comes up with a number of "513, which is correct and located in cell "U214", but instead of giving me data in cell "T214" (13-May-05) it comes up with a completely random date of 1-May-11.

If anyone has any suggestions on how to fix this I would greatly appreciate it. This makes absolutely now sense to me and I'm not even sure how to go about fixing this. If you have any questions or need more information just let me know and I will see what I can do.

John

 

[venkat1926]

is it because result vector (column R) earlier than lookup vector (column S)? just as in vlookup lookup value must be in the first column of the range. otherwise use index match.

besides are the vectors sorted?

see this quoted form excel help

Important The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.