Pulling data from one cell if blank from another

[greg93]

Hi all,

So I have two sets of data like below
ColumnA column B
Row 1 Worker 1 H
Row 2 Worker 2

Data number 2.
ColumnA column B
Row 1 Worker 1 8
Row 2 Worker 2 8

In a third data set in cell B1 I am trying to return the result from data A, row 1 column B, so in this case the H. However if the cell is blank I want it to return the data in the second set of data, Row 1 Column B, so in this case 8

I have tried ifs and index and am not getting it so hopefully my explanation makes sense and one of you lovely people can help me out

Thanks in advance

 

[awoohaw]

Try this:

Book1
	A	B
1	Worker 1	H
2	Worker 2
3
4
5	Worker 1	8
6	Worker 2	9
7
8
9	Worker 1	H
10	Worker 2	9
Sheet4

Cell Formulas
Range	Formula
B9:B10	B9	=LET(a,A9,lkup1,$A$1:$A$2,lkup2,$A$5:$A$6,reta1,$B$1:$B$2,reta2,$B$5:$B$6,lkupv1,XLOOKUP(a,lkup1,reta1,0,0),lkupv2,XLOOKUP(a,lkup2,reta2,NA(),0),IF(lkupv1=0,lkupv2,lkupv1))

 

[greg93]

Wow, I could have sat here for ever and never gotten that, works great thank you so much!!

 

[awoohaw]

You're welcome. I'm pleased to help.
There are probably some more elegant ways to accomplish it.

Best Wishes.