Forum Discussion
LOOKUP function not returning correct value
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.
