LOOKUP function not returning correct value

%3CLINGO-SUB%20id%3D%22lingo-sub-2281040%22%20slang%3D%22en-US%22%3ELOOKUP%20function%20not%20returning%20correct%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281040%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20a%20spreadsheet%20that%20needs%20to%20see%20the%20value%20in%20one%20column%2C%20lookup%20that%20value%20in%20a%20table%20on%20another%20sheet%2C%20and%20return%20the%20value%20in%20an%20adjacent%20column%20from%20the%20other%20sheet.%20Pretty%20simple%20(I%20would%20think)%20But%26nbsp%3B%20neither%20VLOOKUP%20or%20LOOKUP%20are%20populating%20it%20correctly!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20both%20of%20these%20in%20%3CSTRONG%3ECell%20D4%3C%2FSTRONG%3E%20on%20the%20Estimating%20Weekly%20Schedule%20Tab%3A%3C%2FP%3E%3CP%3E%3DLOOKUP(E4%2CJobs!%24B%242%3A%24B%2436%2CJobs!%24A%242%3A%24A%2436)%26nbsp%3B%20-%20this%20one%20doesnt%20return%20correct%20value%3C%2FP%3E%3CP%3E%3DVLOOKUP(E4%2CJobs!A1%3AB36%2C1%2CFALSE)%20-%20this%20one%20just%20gives%20N%2FA%20error%3C%2FP%3E%3CP%3EWhat%20am%20I%20doing%20wrong%3F%26nbsp%3B%20See%20file%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20want%20column%20F%20on%20the%20Estimating%20Weekly%20Schedule%20Tab%20to%20create%20a%20dependent%20Data%20Validation%20list%20that%20populates%20the%20appropriate%20list%20of%20Items%20from%20the%20Jobs%20tab%20for%20that%20appropriate%20Job%20name.%20But%20for%20some%20reason%20it%20keeps%20starting%20the%20list%20at%20the%20bottom%20each%20time.%26nbsp%3B%20If%20anyone%20can%20help%20with%20that%2C%20please%20let%20me%20know.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281040%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-2282148%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20function%20not%20returning%20correct%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1033245%22%20target%3D%22_blank%22%3E%40noeoliver%3C%2FA%3E%26nbsp%3BFirst%20of%20all%2C%20you%20need%20to%20fix%20the%20column%20and%20row%20references%20in%20the%20INDEX%2FMATCH%20formula.%20Could%20also%20replace%20the%20*%20with%20a%20comma.%20Don't%20understand%20where%20that%20one%20came%20from.%3C%2FP%3E%3CP%3ESecondly%2C%20named%20ranges%20may%20not%20begin%20with%20a%20number%2C%20so%20I%20changed%20the%20item%20that%20caused%20a%20problem%20to%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22%2060ft%20DIVE%20BOAT%20OVERHAUL%22%3C%2FSTRONG%3E.%20Note%26nbsp%3Bthe%20extra%20space%20in%26nbsp%3Bthe%20beginning.%20The%20SUBSTITUTE%20function%20in%20the%20data%20validation%20rule%20will%20now%20also%20replace%20that%20space%20by%20an%20underscore.%20And%20then%20I%20also%20changed%20the%20name%20of%20the%20corresponding%20named%20range%20from%20%22_60_DIVE_BOAT_OVERHAUL%22%20to%26nbsp%3B%22_60%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Eft%3C%2FSTRONG%3E%3C%2FFONT%3E_DIVE_BOAT_OVERHAUL%22.%3C%2FP%3E%3CP%3ENow%20all%20seems%20to%20work%20OK!%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281833%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20function%20not%20returning%20correct%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281833%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%20Thank%20you%20so%20much%20that%20worked!%26nbsp%3B%20although%20the%20system%20put%20*1%20at%20the%20end%20instead%20of%20just%20a%201.%26nbsp%3B%20but%20everything%20works%20great.%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20is%20still%20doing%20something%20weird.%26nbsp%3B%20for%20some%20reason%2C%20It%20wont%20let%20me%20select%20the%20same%20job%20%23%20more%20than%20once.%26nbsp%3B%20(see%20new%20attached).%3C%2FP%3E%3CP%3EAnd%20also%2C%20the%20dependent%20list%20that%20I%20created%20in%20column%20F%20refuses%20to%20see%20the%20values%20for%20the%20%2260ft%20Dive%20Boat%20Overhaul%22%20Job%20Name.%26nbsp%3B%20the%20others%20seem%20to%20work%20but%20not%20that%20one.%26nbsp%3B%20What%20am%20I%20doing%20wrong%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281164%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20function%20not%20returning%20correct%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1033245%22%20target%3D%22_blank%22%3E%40noeoliver%3C%2FA%3E%26nbsp%3BTrying%20INDEX%2FMATCH%20in%20stead.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(Jobs!%24A%242%3A%24A%2437%2CMATCH(E4%2CJobs!%24B%242%3A%24B%2437%2C0)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ein%20D4%20and%20copied%20down%20will%20give%20you%20the%20job%20numbers%20that%20belong%20to%20the%20job%20descriptions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20use%20LOOKUP%2C%20but%20then%20your%20look-up%20vectors%20(i.e%20the%20range%20you%20search%20in)%20should%20be%20sorted%20A%20to%20Z.%20And%20for%20VLOOKUP%20to%20work%20you%20would%20need%20to%20move%20the%20Description%20column%20to%20the%20left%20of%20the%20Job%20number%20column.%20If%20you%20are%20on%20MS365%20you%20could%20use%20XLOOKUP%20to%20overcome%20these%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20respect%20to%20the%20dropdowns%20in%20column%20F%2C%20it%20seems%20that%20all%20your%20named%20ranges%20to%20which%20the%20data%20validation%20lists%20are%20pointing%20(with%20INDIRECT)%20are%20miss-aligned.%20For%20instance%20the%20named%20range%20that%20relates%20to%20%22USS%20HOPPER%208C1%20CN01%22%20or%20row%202%20refers%20to%20items%20on%20row%203%2C%20and%20that's%20similar%20for%20all%20the%20ones%20I%20checked.%20Same%20thing%20for%20the%20named%20ranges%20relating%20to%20job%20numbers.%20E.g.%20the%20one%20for%20%2218-3024%22%20on%20row%202%20points%20to%20data%20on%20row%2027.%20The%20next%20one%20for%20%2218-MARD%22%20on%20row%203%20points%20to%20data%20on%20row%2015%20etc.%20Once%20you%20have%20fixed%20that%20it%20will%20probably%20work%20as%20you%20intend%20it%20to.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am creating a spreadsheet that needs to see the value in one column, lookup that value in a table on another sheet, and return the value in an adjacent column from the other sheet. Pretty simple (I would think) But  neither VLOOKUP or LOOKUP are populating it correctly!

 

I've tried both of these in Cell D4 on the Estimating Weekly Schedule Tab:

=LOOKUP(E4,Jobs!$B$2:$B$36,Jobs!$A$2:$A$36)  - this one doesnt return correct value

=VLOOKUP(E4,Jobs!A1:B36,1,FALSE) - this one just gives N/A error

What am I doing wrong?  See file attached. 

 

I also want column F on the Estimating Weekly Schedule Tab to create a dependent Data Validation list that populates the appropriate list of Items from the Jobs tab for that appropriate Job name. But for some reason it keeps starting the list at the bottom each time.  If anyone can help with that, please let me know. 

3 Replies

@noeoliver Trying INDEX/MATCH in stead.

=INDEX(Jobs!$A$2:$A$37,MATCH(E4,Jobs!$B$2:$B$37,0),1)

in D4 and copied down will give you the job numbers that belong to the job descriptions.

 

You could use LOOKUP, but then your look-up vectors (i.e the range you search in) should be sorted A to Z. And for VLOOKUP to work you would need to move the Description column to the left of the Job number column. If you are on MS365 you could use XLOOKUP to overcome these issues.

 

With respect to the dropdowns in column F, it seems that all your named ranges to which the data validation lists are pointing (with INDIRECT) are miss-aligned. For instance the named range that relates to "USS HOPPER 8C1 CN01" or row 2 refers to items on row 3, and that's similar for all the ones I checked. Same thing for the named ranges relating to job numbers. E.g. the one for "18-3024" on row 2 points to data on row 27. The next one for "18-MARD" on row 3 points to data on row 15 etc. Once you have fixed that it will probably work as you intend it to.

@Riny_van_Eekelen  Thank you so much that worked!  although the system put *1 at the end instead of just a 1.  but everything works great. 

But it is still doing something weird.  for some reason, It wont let me select the same job # more than once.  (see new attached).

And also, the dependent list that I created in column F refuses to see the values for the "60ft Dive Boat Overhaul" Job Name.  the others seem to work but not that one.  What am I doing wrong? 

@noeoliver First of all, you need to fix the column and row references in the INDEX/MATCH formula. Could also replace the * with a comma. Don't understand where that one came from.

Secondly, named ranges may not begin with a number, so I changed the item that caused a problem to

" 60ft DIVE BOAT OVERHAUL". Note the extra space in the beginning. The SUBSTITUTE function in the data validation rule will now also replace that space by an underscore. And then I also changed the name of the corresponding named range from "_60_DIVE_BOAT_OVERHAUL" to "_60ft_DIVE_BOAT_OVERHAUL".

Now all seems to work OK! See attached.