SOLVED

LOOKUP function help

%3CLINGO-SUB%20id%3D%22lingo-sub-3178904%22%20slang%3D%22en-US%22%3ELOOKUP%20function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3178904%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20problem%20with%20LOOKUP%20(WYSZUKAJ%20in%20Polish%20version).%20I%20have%20a%20long%20list%20of%20part%20names%20with%20manufacturer%20name%2C%20category%2C%20subcategory%2C%20addidtional%20parameter%20and%20set%20of%20prices%20in%20every%20line.%20On%20another%20sheet%20I%20want%20to%20type%20just%20a%20part%20name%20and%20get%20coresponding%20price.%20I%20was%20trying%20to%20use%20LOOKUP%20(and%20vertical%20and%20horizontal)%20and%20always%20get%20random%20values.%20While%20testing%20it%20I%20discovered%2C%20that%20even%20for%20small%20set%20of%20data%20it%20doesn't%20work%20as%20I%20expected.%3CBR%20%2F%3E%3CBR%20%2F%3EBelow%20you%20can%20see%20six%20tables%20which%20are%20just%20first%20table%20with%20sequent%20values%20typed%20in%20blue%20cell.%20In%20yellow%20cell%20I%20expected%20result%20as%20in%20the%20first%20or%20third%20table.%20Of%20course%2C%20all%20references%20are%20valid.%20You%20can%20also%20see%20formula%20which%20is%20the%20same%20in%20all%20yellow%20cells%20regarding%20shift%20of%20those%20cells.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20should%20I%20do%20to%20achieve%20expected%20results%3F%20Typing%20Four%20in%20blue%20cell%20should%20give%20'4'%20in%20yellow%20cell.%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20is%20no%20data%20type%20mismatch.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MrZiKE_0-1645108638219.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349073iBB2509EEE29E635F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MrZiKE_0-1645108638219.png%22%20alt%3D%22MrZiKE_0-1645108638219.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZiKE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3178904%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3179234%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3179234%22%20slang%3D%22en-US%22%3EThe%20issue%20is%20the%20words%20are%20not%20sort%20in%20ascending%20order%20so%20the%20LOOKUP%20fails.%3CBR%20%2F%3EFrom%20Microsoft%3A%3CBR%20%2F%3EImportant%3A%20The%20values%20in%20array%20must%20be%20placed%20in%20ascending%20order%3A%20...%2C%20-2%2C%20-1%2C%200%2C%201%2C%202%2C%20...%2C%20A-Z%2C%20FALSE%2C%20TRUE%3B%20otherwise%2C%20LOOKUP%20might%20not%20return%20the%20correct%20value.%20Uppercase%20and%20lowercase%20text%20are%20equivalent.%3CBR%20%2F%3E%3CBR%20%2F%3EA%20better%20option%20is%20to%20use%20VLOOKUP%20or%20XLOOKUP%20(If%20available).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3179299%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3179299%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20are%20like%204%20different%20'lookup'%20version%20and%20all%20except%20the%20newest%20XLOOKUP%20default%20to%20expecting%20the%20array%20to%20be%20in%20ascending%20order.%20Either%20use%20XLOOKUP%20if%20you%20have%20it%2C%20or%20use%20VLOOKUP%20and%20define%20the%20range_lookup%20to%20be%20'False'%20to%20only%20find%20an%20exact%20match%2C%20or%20use%20INDEX(...%20MATCH(....%20%2C%200)%20)%20and%20note%20the%20MATCH%20must%20have%20that%20last%20parameter%20set%20to%20find%20a%20exact%20match%20also.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, I have a problem with LOOKUP (WYSZUKAJ in Polish version). I have a long list of part names with manufacturer name, category, subcategory, addidtional parameter and set of prices in every line. On another sheet I want to type just a part name and get coresponding price. I was trying to use LOOKUP (and vertical and horizontal) and always get random values. While testing it I discovered, that even for small set of data it doesn't work as I expected.

Below you can see six tables which are just first table with sequent values typed in blue cell. In yellow cell I expected result as in the first or third table. Of course, all references are valid. You can also see formula which is the same in all yellow cells regarding shift of those cells.

What should I do to achieve expected results? Typing Four in blue cell should give '4' in yellow cell.

There is no data type mismatch.

 

MrZiKE_0-1645108638219.png

Best regards,

 

ZiKE

2 Replies

The issue is the words are not sorted in ascending order so the LOOKUP fails.
From Microsoft:
Important: The values in array 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.

A better option is to use VLOOKUP or XLOOKUP (If available).

best response confirmed by MrZiKE (Occasional Visitor)
Solution

There are like 4 different 'lookup' version and all except the newest XLOOKUP default to expecting the array to be in ascending order. Either use XLOOKUP if you have it, or use VLOOKUP and define the range_lookup to be 'False' to only find an exact match, or use INDEX(... MATCH(.... , 0) ) and note the MATCH must have that last parameter set to find a exact match also.