Is there a shortcut that I can use like vlookup or any other function?
Hello everyone, I’m trying to autofill E19 to E27 based on the values above. Only columns D, E and F are being used here. Since the truck and truck company will be repeated (not in order) I just need to autofill the plate column for the missing values from the obtained info instead of doing manually which will take a long time. Is there a shortcut that I can use like vlookup or any other function?
TIA
Replies
Set up a Lookup Table with All Trucks and Matching Plates in 2 Separate Columns . Use: . ✧ INDEX and MATCH ✧ VLOOKUP ✧ XLOOKUP
. ✧ INDEX and MATCH . ✧ VLOOKUP . ✧ XLOOKUP (not working with older Excel versions) https://exceljet.net/articles/index-and-match https://exceljet.net/functions/vlookup-function https://exceljet.net/functions/xlookup-function
Kent Paulsson hi, I was able to use index and lookup but I’m not sure why some are returning a value of zero. Example: cells E41 and E44 are 0 even though I have values for them above. Any way I can fix that?
Josue Criollo . Are you sure info is Exact Match on both sides ? . Check both CELLs and see if them are Exact Match... . You can try to use =LEN(CELL.REF.) . If same should not be a problem if not one is Number and other one is Text, even if look exactly the same... . =ISTEXT(CELL REF.) or =ISNUMBER(CELL.REF.) . =IF(ISTEXT(CELL.REF.),LEN(CELL.REF.),"Not Text") or =IF(ISNUMBER(CELL.REF.),LEN(CELL.REF.),"Not Number")
Kent Paulsson they are the same as seen in the first data set. I also checked if they are either text or number and they match. This happens to some but not all for some reason
Josue Criollo The only thing I can think of it is that you missed a $ sign in your Formula/Function so Range is moving, when it should be fixed/locked, if copy or drag down Formula/Function
Kent Paulsson they are locked 😕 thanks for trying tho
Josue Criollo Sensitive info or you can share the file and say what is your error rows...
Kent Paulsson can i share the file via Dropbox maybe you can take a look? That’d be great
Josue Criollo Not sure option to Share, but I can get in DM and email
Kent Paulsson sent DM!
=CHAR(96+MOD(ROW()-ROW($E$10),3)+1)&INT(ROWS($E$8:E10)/3) Enter in cell E10 and copy it down
The best way is to have the plates and and trucks in separate column then use if condition with and function. Of condition true, return the vale else you can mention anything which you want like comment or empty values
