Lookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2119741%22%20slang%3D%22en-US%22%3ELookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119741%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%26nbsp%3B%20I%20am%20working%20with%20a%20Lookup%20function.%26nbsp%3B%20The%20lookup%20vector%20is%20in%20the%20left%20column%2C%20and%20the%20return%20in%20a%20column%20to%20the%20right%2C%20the%20function%20area%20includes%20the%20entire%20lookup%20vector%20and%20return%20vector.%26nbsp%3B%20The%20formula%20is%20repeated%20down%20a%20column%20and%20is%20correct%20for%20each%20entry%2C%20not%20copied%20where%20the%20value%20of%20the%20vector%20area%20or%20return%20area%20changes.%26nbsp%3B%20Many%20returns%20for%20the%20value%20entered%20are%20correct%2C%20but%20some%20entries%20do%20not%20return%20the%20correct%20value.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20evaluation%20also%20returns%20the%20incorrect%20value.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formatting%20is%20the%20same%20for%20all%20entries%20in%20the%20columns.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20evaluation%20formula%20returns%20the%20correct%20values%20for%20each%20of%20the%20cells%20in%20the%20formula.%3C%2FP%3E%3CP%3ENonetheless%2C%20the%20return%20value%20of%20the%20lookup%20is%20incorrect.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20for%20correcting%20this%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2119741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2122330%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2122330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961604%22%20target%3D%22_blank%22%3E%40Deeann_Speigle%3C%2FA%3E%26nbsp%3BUse%20VLOOKUP%20in%20stead.%20In%20H1%2C%20and%20copy%20down.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(G1%2C%24A%241%3A%24B%24198%2C2%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20order%20for%20LOOKUP%20to%20work%2C%20the%20lookup%20range%20need%20to%20be%20sorted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20use%20XLOOKUP.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(G1%3AG14%2CA1%3AA198%2CB1%3AB198%2C%2C0%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2122321%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2122321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay.%26nbsp%3B%20Here%20is%20a%20screen%20shot%20of%20the%20data%2C%20the%20actual%20workbook%20is%20included%20as%20attachment.%26nbsp%3B%20The%20sheet%20is%20rather%20messy%2C%20sorry.%26nbsp%3B%20It%20is%20a%20work%20in%20the%20initial%20stages.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lookup%20function.png%22%20style%3D%22width%3A%20693px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254310i1C401DB12BB03A69%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22lookup%20function.png%22%20alt%3D%22lookup%20function.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20screen%20shot%20of%20the%20worksheet.%26nbsp%3B%20column%20A%20is%20a%20reference%20or%20catalogue%20number%2C%20column%20B%20is%20the%20value%20of%20the%20item%20in%20column%20A.%26nbsp%3B%20In%20column%20G%20the%20reference%20number%20is%20entered%2C%20this%20also%20the%20number%20that%20is%20used%20in%20the%20lookup%20function%20that%20is%20in%20column%20H.%26nbsp%3B%20The%20expected%20return%20is%20the%20number%20corresponding%20to%20the%20reference%20number.%26nbsp%3B%20In%20column%20F%20an%20x%20marks%20the%20incorrect%20returns%20for%20the%20lookup%20value%20entered.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-align-right%22%3EThe%20sheet%20was%20created%20to%20list%20food%20items%20and%20calorie%20values%20in%20columns%20A%2C%20B%20respectively.%26nbsp%3B%20So%2C%20when%20a%20food%20item%20is%20entered%20in%20column%20G%2C%20the%20return%20is%20the%20correct%20calorie%20number%20associated%20with%20the%20food%20item.%26nbsp%3B%20For%20example%2C%20A2%20is%201%2F2%20cup%20of%20oats%20and%20B2%20defines%20the%20total%20calories%20as%20150.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20an%20incorrect%20value%20was%20returned%2C%20I%20entered%200s%20for%20all%20of%20the%20blank%20values%20in%20column%20B.%26nbsp%3B%20Then%20I%20checked%20formatting%2C%20copy%20and%20paste%20error%20(because%20I%20used%20the%20same%20formula%20for%20all%20of%20column%20H)%2C%20then%20I%20checked%20the%20value%20entered%20with%20a%20true%2Ffalse%20function%2C%20then%20I%20rewrote%20the%20function%20to%20include%20only%20200%20values%20in%20the%20lookup%20and%20return%20columns%20(I%20wasn't%20sure%20if%20there%20was%20a%20limit%20to%20the%20lookup%20function).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20what%20else%20to%20try.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119758%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961604%22%20target%3D%22_blank%22%3E%40Deeann_Speigle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20attach%20you%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2125799%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2125799%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOoh%2C%20that%20is%20fun.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello.  I am working with a Lookup function.  The lookup vector is in the left column, and the return in a column to the right, the function area includes the entire lookup vector and return vector.  The formula is repeated down a column and is correct for each entry, not copied where the value of the vector area or return area changes.  Many returns for the value entered are correct, but some entries do not return the correct value.  

The evaluation also returns the incorrect value.  

The formatting is the same for all entries in the columns.  

An evaluation formula returns the correct values for each of the cells in the formula.

Nonetheless, the return value of the lookup is incorrect.  

Any suggestions for correcting this problem?

 

 

Thank you.

 

4 Replies

@Deeann_Speigle 

Kindly attach you sample file.

@Twifoo 

 

Okay.  Here is a screen shot of the data, the actual workbook is included as attachment.  The sheet is rather messy, sorry.  It is a work in the initial stages.

lookup function.png

 

Here is a screen shot of the worksheet.  column A is a reference or catalogue number, column B is the value of the item in column A.  In column G the reference number is entered, this also the number that is used in the lookup function that is in column H.  The expected return is the number corresponding to the reference number.  In column F an x marks the incorrect returns for the lookup value entered.  

 

The sheet was created to list food items and calorie values in columns A, B respectively.  So, when a food item is entered in column G, the return is the correct calorie number associated with the food item.  For example, A2 is 1/2 cup of oats and B2 defines the total calories as 150.

 

When an incorrect value was returned, I entered 0s for all of the blank values in column B.  Then I checked formatting, copy and paste error (because I used the same formula for all of column H), then I checked the value entered with a true/false function, then I rewrote the function to include only 200 values in the lookup and return columns (I wasn't sure if there was a limit to the lookup function). 

 

Not sure what else to try.   

  

 

@Deeann_Speigle Use VLOOKUP in stead. In H1, and copy down.

=VLOOKUP(G1,$A$1:$B$198,2,0)

In order for LOOKUP to work, the lookup range need to be sorted.

 

Alternatively, use XLOOKUP.

=XLOOKUP(G1:G14,A1:A198,B1:B198,,0,1)

 

@Riny_van_Eekelen 

 

Ooh, that is fun.  Thank you!