I was just trying out the old LOOKUP function of excel, but every time it is returning me few wrong values. What's wrong with this lookup function?
Yeah I know there are better formulas like vlookup, index/match, the latest xlookup to do the same thing. But I just wanna wanna know what I'm doing wrong over here? or the lookup function itself has any bug? Please correct me
I'm attaching the excel file below.
Last edited by csupra; 01-18-2020 at 12:41 PM.
Welcome to the forum.
Nothing is wrong, but it IS the wrong function for the job.
LOOKUP does not have a lookup type argument, so the lookup values must be in order. If you order the table alphabetically by name, then the results will be correct.
Excel 2016 (Windows) 32 bit
Lookup table Result Table Name Country Profession Name Reg_No Alen USA Designer Taylor Bob German Youtuber John Chris USA Data Analyst Alen John Italy Painting Bob Martin Australia Business Martin Taylor UK Software Engineer Chris
Sheet: Sheet1
Here a VLOOKUP with the final argument set at 0 (FALSE) will return an exact result.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The data in the lookup table has to be sorted, otherwise you will get wrong results. Sort A3:D8 by Name, and you will get the correct results.
Hope this helps.
Pete
As explained in the LOOKUP() function help file:The LOOKUP() function can only use a binary search type algorithm that requires the lookup values to be sorted. In your file, the names in column A are not sorted, so the LOOKUP() function's binary search algorithm gets lost.Originally Posted by MS Excel help
If you can sort the lookup table, then you can use the LOOKUP() function just fine. One of the advantages for the "newer" functions is that they have an "exact match" option that does not require the lookup table to be sorted.
HI,
If you remember lookup works for sorted ascending fields, check the attached file.
Would recommend to search you post before posting, Its already discussed "Lookup Returning Wrong Value"
* Mark the thread as Solved, and hit * to say Thanks.
in your case
=LOOKUP(2,1/(F3=$A$3:$A$8),$B$3:$B$8)
Thank you! I skipped the part of sorting.
tim201110 :-) It show the possibility to use LOOKUP even others can be used. Let me remember the TIP from MS
Tip: We strongly recommend using VLOOKUP or HLOOKUP instead of the array form. See this video about VLOOKUP; it provides examples. The array form of LOOKUP is provided for compatibility with other spreadsheet programs, but it's functionality is limited.
Remember you don't need to sort if you use an appropriate function like VLOOKUP.
i like itbut it's functionality is limited
=SUMPRODUCT(LOOKUP(H3:H11,A1:A11,D1:D11))
and
=SUM(VLOOKUP(INDEX(H:H,N(IF(1,ROW(H3:H11)))),A1:D11,4)) array
what did they mean by limited?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks