The Excel Lookup function is misbehaving for me.
Row 83 is the Max value in the column above.
Row 84 is supposed to find the date at which the maximum occurs.
It works correctly for column F and H.
It fails miserably for column B and E.
Have you triple-checked your cell references in those columns?
I don’t have excel at hand for details but I’ve done similar with combinations of MATCH, INDEX and INDIRECT…
Rule 303 said:
Have you triple-checked your cell references in those columns?
How would that have an effect, I mean 7362 is 7362. Max should give the exact maximum. The Lookup statements are all direct copies of the one shown at the top of the screen.
furious said:
I don’t have excel at hand for details but I’ve done similar with combinations of MATCH, INDEX and INDIRECT…
Ta, I’ll see if one of those works.
mollwollfumble said:
The Excel Lookup function is misbehaving for me.Row 83 is the Max value in the column above.
Row 84 is supposed to find the date at which the maximum occurs.It works correctly for column F and H.
It fails miserably for column B and E.
Can you share the file so we may inspect?
mollwollfumble said:
Rule 303 said:
Have you triple-checked your cell references in those columns?
How would that have an effect, I mean 7362 is 7362. Max should give the exact maximum. The Lookup statements are all direct copies of the one shown at the top of the screen.
If the range in 84 was incorrect, 83 would still return the right number, but 84 would read a different column.
That’s the only suggestion I’ve got, sorry mate.
You already know the column, MATCH can tell you the row…
dv said:
mollwollfumble said:
The Excel Lookup function is misbehaving for me.Row 83 is the Max value in the column above.
Row 84 is supposed to find the date at which the maximum occurs.It works correctly for column F and H.
It fails miserably for column B and E.
Can you share the file so we may inspect?
That’s a good question. Try this. Let me know if you can/can’t read it.
https://drive.google.com/file/d/1nkVBmSThw6jej7U4HUvjeytdWuY6BC3i/view?usp=sharing
Last worksheet.
The function match() has exactly the same problem as lookup(), it fails and works for the same columns.
All your lists of numbers, are they values entered into cells, or are they formulas? Have you tried, for example, VALUE as the lookup?
furious said:
All your lists of numbers, are they values entered into cells, or are they formulas? Have you tried, for example,VALUE(B83)as the lookup?
Fixed…
furious said:
All your lists of numbers, are they values entered into cells, or are they formulas? Have you tried, for example, VALUE as the lookup?
Almost all of these numbers I typed in by hand. But will try.
=LOOKUP,B4:B81,$A4:$A81)
Nup, still not working.
Playing with the numbers and formats and such, I don’t think the LOOKUP function is actually working in any of the columns, mate.
Test by changing the values in a few cells to be the highest value in the column, then observe the effect on Row 84.
There’s a trick that changes dates to values – Think you need that.
Nope. Doesn’t work.
mollwollfumble said:
furious said:
All your lists of numbers, are they values entered into cells, or are they formulas? Have you tried, for example, VALUE as the lookup?
Almost all of these numbers I typed in by hand. But will try.
=LOOKUP,B4:B81,$A4:$A81)
Nup, still not working.
If you haven’t solved it by the morning I’ll fire up the windows machine and take a look…
furious said:
mollwollfumble said:
furious said:
All your lists of numbers, are they values entered into cells, or are they formulas? Have you tried, for example, VALUE as the lookup?
Almost all of these numbers I typed in by hand. But will try.
=LOOKUP,B4:B81,$A4:$A81)
Nup, still not working.
If you haven’t solved it by the morning I’ll fire up the windows machine and take a look…
It’s OK, don’t bother. I’ve typed in the correct numbers by hand. That solved the problem. Thanks for trying.
mollwollfumble said:
furious said:
mollwollfumble said:Almost all of these numbers I typed in by hand. But will try.
=LOOKUP,B4:B81,$A4:$A81)
Nup, still not working.
If you haven’t solved it by the morning I’ll fire up the windows machine and take a look…
It’s OK, don’t bother. I’ve typed in the correct numbers by hand. That solved the problem. Thanks for trying.
No, doing things the hard way is unacceptable. Even if it is quicker than automating it…
moll,
I literally just copied the contents of Cell E84, and pasted into Cells B84 to whatever84, and it worked.
dv said:
moll,I literally just copied the contents of Cell E84, and pasted into Cells B84 to whatever84, and it worked.
You don’t suppose he has got calculations turned off? What is it? F9 to calculate?
Come to think of it, the same effect works if I just took B84 and copied and pasted onto B84…
Just seems like anything needs to happen to force it to recompute.
If your columns are not in numerical order, you need to set VLOOKUP (or MATCH) to find an exact match.
For VLOOKUP use:
=VLOOKUP
I’ve got it working.
VLOOKUP function. Date need to be to the right of the data.
The Rev Dodgson said:
If your columns are not in numerical order, you need to set VLOOKUP (or MATCH) to find an exact match.For VLOOKUP use:
=VLOOKUP
Press quote to see what I actually entered.
Looking at your image, it looks like non-sequential lists are your problem.
If that doesn’t work, please post the actual spreadsheet.
Rule 303 said:
I’ve got it working.VLOOKUP function. Date need to be to the right of the data.
VLOOKUP has the same requirement for lists to be in numerical order, unless you specify an exact lookup (which is not the default).
Also note that Lookup is only provided for backward compatibility, and numbers need to be in ascending order.
Use Vlookup, or Match and Index to return a value in column A from a match in a column to the right.
The Rev Dodgson said:
Rule 303 said:
I’ve got it working.VLOOKUP function. Date need to be to the right of the data.
VLOOKUP has the same requirement for lists to be in numerical order, unless you specify an exact lookup (which is not the default).
I copied Col A (the dates) to the last column (W) and put this in V84
=VLOOKUP
which works.
Currently racking my brain to get it to work in other columns.
=VLOOKUP (V83,V4:W81,2,FALSE)
without the space ^
Got it.
=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
That is inelegant…
furious said:
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
That is inelegant…
Can you relative ref that 21 so he can just c&p across all columns, chief?
furious said:
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
That is inelegant…
Yep. Ugly as a hatfull of arseholes, but it works.
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
Yes, it’s the FALSE that is needed to get an exact lookup to make it work with numbers in any order (as long as the lookup value is an exact match for one of them).
For this case Index and Match is probably easier:
=INDEX)
That can then be copied across to all the other columns.
The Rev Dodgson said:
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
Yes, it’s the FALSE that is needed to get an exact lookup to make it work with numbers in any order (as long as the lookup value is an exact match for one of them).
For this case Index and Match is probably easier:
=INDEX)
That can then be copied across to all the other columns.
OK, how do you make the function not disappear after the ( ?
dv said:
furious said:
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
That is inelegant…
Can you relative ref that 21 so he can just c&p across all columns, chief?
NAFAIK. Have to manually enter the column number 20 times.
The Rev Dodgson said:
The Rev Dodgson said:
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
Yes, it’s the FALSE that is needed to get an exact lookup to make it work with numbers in any order (as long as the lookup value is an exact match for one of them).
For this case Index and Match is probably easier:
=INDEX)
That can then be copied across to all the other columns.OK, how do you make the function not disappear after the ( ?
Add a space in the formula before the (
The Rev Dodgson said:
The Rev Dodgson said:
Rule 303 said:
Got it.=VLOOKUP (B83,B4:V81,21,FALSE)
Where 21 is the distance to the far right column, where the dates are.
Yes, it’s the FALSE that is needed to get an exact lookup to make it work with numbers in any order (as long as the lookup value is an exact match for one of them).
For this case Index and Match is probably easier:
=INDEX($A4:$A81,MATCH(B83,B4:B81,0))
That can then be copied across to all the other columns.OK, how do you make the function not disappear after the ( ?
It’s like that, and that’s the way it is…
Rule 303 said:
dv said:
furious said:That is inelegant…
Can you relative ref that 21 so he can just c&p across all columns, chief?
NAFAIK. Have to manually enter the column number 20 times.
Use Match and Index, then you don’t need a column offset.
furious said:
The Rev Dodgson said:
The Rev Dodgson said:Yes, it’s the FALSE that is needed to get an exact lookup to make it work with numbers in any order (as long as the lookup value is an exact match for one of them).
For this case Index and Match is probably easier:
=INDEX($A4:$A81,MATCH(B83,B4:B81,0))
That can then be copied across to all the other columns.OK, how do you make the function not disappear after the ( ?
It’s like that, and that’s the way it is…
Run…
The Rev Dodgson said:
Rule 303 said:
dv said:Can you relative ref that 21 so he can just c&p across all columns, chief?
NAFAIK. Have to manually enter the column number 20 times.
Use Match and Index, then you don’t need a column offset.
I said that ages ago…
Rule 303 said:
The Rev Dodgson said:
The Rev Dodgson said:Yes, it’s the FALSE that is needed to get an exact lookup to make it work with numbers in any order (as long as the lookup value is an exact match for one of them).
For this case Index and Match is probably easier:
=INDEX ($A4:$A81,MATCH (B83,B4:B81,0))
That can then be copied across to all the other columns.OK, how do you make the function not disappear after the ( ?
Add a space in the formula before the (
Thanks, now fixed.
furious said:
The Rev Dodgson said:
Rule 303 said:NAFAIK. Have to manually enter the column number 20 times.
Use Match and Index, then you don’t need a column offset.
I said that ages ago…
Yeah, but the answer to the original problem is using an exact match. VLOOKUP and INDEX/MATCH will still give the wrong answers with the default settings.
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
Sometimes, just sometimes, my answers are serious…
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
What do you mean it’s fine?
It only gives the right answer when the values are in increasing order?
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
F9, perhaps?
furious said:
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
F9, perhaps?
It’s got nothing to do with recalculating.
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
I believe the data needs to be to the left of the dates for any of the Lookups to work.
Rule 303 said:
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
I believe the data needs to be to the left of the dates for any of the Lookups to work.
Hence, MATCH…
Rule 303 said:
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
I believe the data needs to be to the left of the dates for any of the Lookups to work.
That’s why using Index/Match is easier in this case.
The Rev Dodgson said:
Rule 303 said:
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
I believe the data needs to be to the left of the dates for any of the Lookups to work.
That’s why using Index/Match is easier in this case.
Which is what I said ages ago…
furious said:
The Rev Dodgson said:
Rule 303 said:I believe the data needs to be to the left of the dates for any of the Lookups to work.
That’s why using Index/Match is easier in this case.
Which is what I said ages ago…
.. without addressing the problem Moll had with getting the wrong answers.
The Rev Dodgson said:
furious said:
The Rev Dodgson said:That’s why using Index/Match is easier in this case.
Which is what I said ages ago…
.. without addressing the problem Moll had with getting the wrong answers.
I could have turned on my windows machine and solved it completely or I could give him a nudge in the right direction. Give a man a fish he’ll eat for the day, teach a man to fish etc.etc.
furious said:
The Rev Dodgson said:
furious said:Which is what I said ages ago…
.. without addressing the problem Moll had with getting the wrong answers.
I could have turned on my windows machine and solved it completely or I could give him a nudge in the right direction. Give a man a fish he’ll eat for the day, teach a man to fish etc.etc.
But using INDEX/MATCH is not a nudge in the right direction. It has the same problem as VLOOKUP.
The Rev Dodgson said:
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
What do you mean it’s fine?
It only gives the right answer when the values are in increasing order?
No it doesn’t. It gives the right answer.
I did the experiment. 8-| see below.
The Rev Dodgson said:
furious said:
The Rev Dodgson said:.. without addressing the problem Moll had with getting the wrong answers.
I could have turned on my windows machine and solved it completely or I could give him a nudge in the right direction. Give a man a fish he’ll eat for the day, teach a man to fish etc.etc.
But using INDEX/MATCH is not a nudge in the right direction. It has the same problem as VLOOKUP.
You suggested INDEX/MATCH as a solution but when I said I suggested that already you say it isn’t the solution?
dv said:
The Rev Dodgson said:
dv said:
I mean really… his current formula is fine and he just needs to c&p it to force it to calculate or do something else to recalculate…
What do you mean it’s fine?
It only gives the right answer when the values are in increasing order?
No it doesn’t. It gives the right answer.
I did the experiment. 8-| see below.
I see nothing below.
More derails on the problems with VLOOKUP or MATCH, + a VBA function to solve them:
For those getting monthly updates of Office 365, there is now an XLOOKUP function, which deals with both of Moll’s problems:
- You can lookup the nearest value using unsorted data.
- You can define a fixed range for the return value range, as with LOOKUP, so you don’t need INDEX/MATCH.
The Rev Dodgson said:
More derails on the problems with VLOOKUP or MATCH, + a VBA function to solve them:For those getting monthly updates of Office 365, there is now an XLOOKUP function, which deals with both of Moll’s problems:
- You can lookup the nearest value using unsorted data.
- You can define a fixed range for the return value range, as with LOOKUP, so you don’t need INDEX/MATCH.
Just checking XLOOKUP, if there is no exact match, you can set it to return the next highest value, or the next lowest value, but not the nearest value, which seems a little odd.
The Rev Dodgson said:
The Rev Dodgson said:
More derails on the problems with VLOOKUP or MATCH, + a VBA function to solve them:For those getting monthly updates of Office 365, there is now an XLOOKUP function, which deals with both of Moll’s problems:
- You can lookup the nearest value using unsorted data.
- You can define a fixed range for the return value range, as with LOOKUP, so you don’t need INDEX/MATCH.Just checking XLOOKUP, if there is no exact match, you can set it to return the next highest value, or the next lowest value, but not the nearest value, which seems a little odd.
Ta. Thank you. There darn well ought to be an exact match. Both numbers are exact integers.
mollwollfumble said:
The Rev Dodgson said:
The Rev Dodgson said:
More derails on the problems with VLOOKUP or MATCH, + a VBA function to solve them:For those getting monthly updates of Office 365, there is now an XLOOKUP function, which deals with both of Moll’s problems:
- You can lookup the nearest value using unsorted data.
- You can define a fixed range for the return value range, as with LOOKUP, so you don’t need INDEX/MATCH.Just checking XLOOKUP, if there is no exact match, you can set it to return the next highest value, or the next lowest value, but not the nearest value, which seems a little odd.
Ta. Thank you. There darn well ought to be an exact match. Both numbers are exact integers.
Does your Excel have XLookup though?
If not, the easiest solution is:
=INDEX ($A4:$A81,MATCH (B83,B4:B81,0))
but don’t forget the final 0 in the Match arguments, which will only return an exact match.
Bump for Moll.
Is this sorted now?
