Filling up blank cells with previous or next value based on a criteria from another column

%3CLINGO-SUB%20id%3D%22lingo-sub-2805077%22%20slang%3D%22en-US%22%3EFilling%20up%20blank%20cells%20with%20previous%20or%20next%20value%20based%20on%20a%20criteria%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805077%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20dataset%20and%20there%20are%20some%20cells%20which%20are%20blank.%20I%20have%20to%20either%20fill%20up%20with%20previous%20or%20next%20value%20in%20the%20column%20based%20on%20a%20criteria%20from%20another%20column.%20For%20example%20in%20the%20diagram%20below%2C%20I%20have%20to%20fill%20the%20empty%20cells%20in%20column%20B%20based%20on%20the%20values%20in%20column%20E.%20If%20column%20E2856%20is%2020%2C%20then%20fill%20up%20value%20using%20B2855.%20If%20E2856%20is%2021%20then%20B2856%20should%20be%20filled%20with%20the%20next%20available%20value%20which%20is%20in%20B2868.%20How%20do%20I%20structure%20the%20IF%20statement%20to%20do%20that%20or%20is%20there%20any%20better%20formula%20for%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ameer193_1-1633154947412.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314546i26FD1D908A230E13%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Ameer193_1-1633154947412.png%22%20alt%3D%22Ameer193_1-1633154947412.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2805077%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805202%22%20slang%3D%22en-US%22%3ERe%3A%20Filling%20up%20blank%20cells%20with%20previous%20or%20next%20value%20based%20on%20a%20criteria%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172823%22%20target%3D%22_blank%22%3E%40Ameer193%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22WynHopkins_0-1633162592286.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314552i5270FABA064A5719%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22WynHopkins_0-1633162592286.png%22%20alt%3D%22WynHopkins_0-1633162592286.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20add%202%20helper%20columns%20as%20per%20the%20attached%20file%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a large dataset and there are some cells which are blank. I have to either fill up with previous or next value in the column based on a criteria from another column. For example in the diagram below, I have to fill the empty cells in column B based on the values in column E. If column E2856 is 20, then fill up value using B2855. If E2856 is 21 then B2856 should be filled with the next available value which is in B2868. How do I structure the IF statement to do that or is there any better formula for this? 

Ameer193_1-1633154947412.png

 

Thanks in advance.

 

 

1 Reply

@Ameer193 

 

WynHopkins_0-1633162592286.png

 

I'd add 2 helper columns as per the attached file