I have tow sets of Data
Data 1 Data 2
A B C D
Tank No Gross Tank No Packed
1 7.00 1 6.09
2 5.00 2 5.21
3 7.50 3 7.03
4 4.00 4 3.33
5 6.00 5 2.00
6 7.00 6 3.11
7 7.00 7 8.11
8 5.50 9 5.19
9 8.00 10 7.92
10 9.00 11 6.53
11 7.50 12 7.70
12 8.00 13 5.02
13 8.00 14 8.21
14 9.00 15 3.00
15 7.00 16 6.66
16 8.00 17 4.00
17 4.00 19 5.22
18 8.50 20 7.41
19 9.50 21 5.85
20 8.00
21 8.00
IN the second set of data Tank no 8 and 18 is missing. Now what i want is a result like this.
Tank No Gross Packed
1 7.00 6.09
2 5.00 5.21
3 7.50 7.03
4 4.00 3.33
5 6.00 2.00
6 7.00 3.11
7 7.00 8.11
8 5.50
9 8.00 5.19
10 9.00 7.92
11 7.50 6.53
12 8.00 7.70
13 8.00 5.02
14 9.00 8.21
15 7.00 3.00
16 8.00 6.66
17 4.00 4.00
18 8.50
19 9.50 5.22
20 8.00 7.41
21 8.00 5.85
But when i am using query
=Query(Production,"Select A,B,D where A=C",1)
where Production is a named range of column A B C and D, i am not getting the desired results.
The result stops at Tank No 7. Its like this
Tank No Gross Packed
1 7.00 6.09
2 5.00 5.21
3 7.50 7.03
4 4.00 3.33
5 6.00 2.00
6 7.00 3.11
7 7.00 8.11
How can i get my desired result using query or any other sheet functions. You can find the sheet with data here : https://docs.google.com/spreadsheets/d/1TQvm1jLktVb3JcMSN5J2oKEbZX9u4iQQ7LQECaPbQAE/edit?usp=sharing
Thanks
Not the cleanest in the world but will do the trick
={
Query({Production},"select Col1,Col2",1),
ArrayFormula(IFERROR(VLOOKUP(QUERY({Production},"select Col1"),
Query({Production},"select Col3,Col4",1),2,0),))
}
The differences to pnuts solution (which is the simplest) are