I can modify this if you need a different behavior depending on which columns are empty. This will return 0 if both fields are empty, C if only B is empty and -B if only C is empty. In RESULTS, use the following formula (put this one in row number 1 and copy it down): =VLOOKUP(A1,DATA!A:C,3,FALSE)-VLOOKUP(A1,DATA!A:C,2,FALSE) If I didn't understand your question correctly, feel free to comment and clarify and I will propose something else.Įdit for VLOOKUP solution: I'm assuming that the client IDs are in column A in both tabs and you have to substract B from C in the same row as the ID in the DATA tab. If one of the two columns is empty, it won't do anything, which is what you want. The IF condition checks if both DATA!B1 and DATA!A1 are not empty, in which case it means they both have data and it will substract it. In this post, you will learn adding and subtracting in Excel in one formula with four easy and handy ways. In the results tab, you could just type =IF(AND(DATA!B1"",DATA!A1""),DATA!B1-DATA!A1,"")Īnd copy the formula down the whole column. 2482 3 Get FREE Advanced Excel Exercises with Solutions Adding and subtracting are the two most common mathematical operations that we do in our everyday lives. I don't actually get why you need to use VLOOKUP to substract two columns.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |