need a formula to distribute the values of two ranges on the another Range based on cond.

[ajl_ahmed]

Hi
I Have two ranges; Range 1 K5:L8, Range 2, O5:Q8.
I need to distribute the values of Both ranges on another Rang 3, B6:B15 based on matching and the value of Q (Q5:Q8) such that if the value cell Q is 1, the name in the range B6:B15 has the value 50 otherwise it takes its value from L5:L8

 Attached Files

[#2 · Accepted solution]

@ajl_ahmed 

Range 1 and Range 2 only has Names 1 to Names 4.

B6:B15 also has Names 5 to Names 10. Should we leave their value empty?

If so, enter the following formula in C6:

=IF(XLOOKUP(B6,$O$5:$O$8,$Q$5:$Q$8,"")=1,XLOOKUP(B6,$O$5:$O$8,$P$5:$P$8),XLOOKUP(B6,$K$5:$K$8,$L$5:$L$8,""))

and fill down.