Log In
Register
Home
Active Member
August 28, 2018
Offline
Hi,
My first post, so please forgive me if I do something wrong.
I have been using this exact formula on a sheet and it works fine except on a few lines (cells): =IF(O4>0,(LOOKUP((MIN(G4:L4)), G4:L4, G$1:L$1))," ")
I have played with the formatting of all cells involved and nothing seems to work.
Any help would be appreciated.
Thank you,
brckwrth
VIP
April 21, 2015
Offline
Hi Brian, welcome here at Mynda's Forum.
The difference I see in the values in the rows 4 and 5 (more exactly G4:L5) is they are maybe not values but text. The differ from the ones in the other rows, because there you have the $ amount also given.
So maybe formatting these cells wil solve your question?
Frans
Active Member
August 28, 2018
Offline
Hi Frans,
Thank you for your reply.
However, I have tried every combination of formatting - Text, General, Accounting, Number ... etc. Also copying the formats of the lines above and below. All to no avail.
Also tried the Ctrl-Shift-Enter to add the parentheses brackets { }.
That did not work either.
Signed,
Stymied
October 5, 2010
Offline
Hi Brian,
If you can supply the workbook it makes it easier to work out what is going wrong.
Regards
Phil
VIP
Trusted Members
June 25, 2016
Offline
Hi Brian
LOOKUP requires the lookup vector (e.g. G2:L2) to be sorted in ascending order (which you can't do in your example), otherwise it may give you erroneous result.
I would suggest you use INDEX/MATCH instead of LOOKUP.
You can try this. In cell P2 enter =IF(O2>0,INDEX($G$1:$L$1,MATCH(MIN(G2:L2),G2:L2,0)),"")
Hope this helps.
Sunny
Active Member
August 28, 2018
Offline
Thank you very much Sunny! Your INDEX formula worked !
Philip, I would still be curious to know why some of the LOOKUP Cells work and others don't. So I've attached a version of the sheet here.
The sort order does not seem to matter on some of the successful lines. Lines 5, 7, 8 - for examples.
Anyway, thank you Myonlinetraininghub Community!
B
VIP
April 21, 2015
Offline
Thanks for the sheet Brian. It helped to look for some faults. I worked some time with it and try to solve the problem, but didn't succeed. Even not with a new matrix with the same values.
I thought I found some things, but everytime the inconsistent working of the formula remained.
My documentation also said that this formula needs to have the data sorted, just as Sunny said. You seem to have good results in some lines, but when you enter for instance in a row that works good a lower value in a empty cell, you'll see that most of the times the same error is given. So it has to do with some inconsistency we can't reproduce exactly and therefore also not explain.
Fine that another formula works properly, so you can use that one!
Just one remark: you painted the cells with the highest and lowest values by hand. You better use conditional formatting for that!
Frans
VIP
Trusted Members
June 25, 2016
Offline
Hi Brian
Nice to know the formula is working for you.
LOOKUP (or VLOOKUP/HLOOKUP with the last argument TRUE/1/blank) does an approximate search for a value using a Binary search instead of a Sequential search.
Basically it compares your lookup value against the middle of the lookup vector to determine which side of the vector the lookup value it is located.
It will then continue to halve the lookup vector until it finds a value that is equal to or less than the lookup value.
If it fails to find one, it will give the #N/A error. Because of the Binary search, your lookup vector MUST be sorted otherwise the answer will be erratic.
You will get a correct answer in certain cases and not in another, depending on what your lookup array looks like.
Hope I am not confusing you 
.
Cheers
Sunny
1 Guest(s)
