Populate list based on criterias on other lists in Excel

frickinFrodo 0
Viewed 18

In this problem I will provide you with the situation, and a picture for name reference and visualization of the problem.

I have list called ID in column C. The list is populated, but some rows are blank. I also have two lists in column E and F, called List1 and List2.

I want to create List3: List3 shall copy the values from list1, but copy the values from list2 for the rows where list2 is populated (list2 dominates list1). At the same time - copying shall only take place for the rows where ID-column is populated. This means that List3 shall blank for the same rows where ID-column blanks.

Do anyone have a solution to my problem? In advance, thank you!

(PS: If there's a way to make list3 dynamic with the total length of ID-column, that would also be great, but not necessary.)

Picture provided here.

excel
1 Answers

Assuming your columns given, you can use this formula:

= IF(C2<>"",IF(F2<>"",F2,E2),"")

It first checks if there are data in colum C, if yes, it checks colum F: if there is a value, takes it, otherwise takes the one from E.

If there is no value in C, it returns nothing

To spill the values down you can use:

=BYROW(C12:F16,LAMBDA(d,
                LET(ID,INDEX(d,1,1),
                    List1,INDEX(d,1,3),
                    List2,INDEX(d,1,4),
                    IF(ID<>"",IF(List2<>"",List2,List1),""))))
useryPy710
Related