vlookup with multiple returned data

[rfcomm2k]

I have created a table with 5 columns. Column 1 (length) is the total length needed for a given beam. Column 2 is the first quantity, column 3 is first size, column 4 is second quantity, column 5 is second size. See below.

I also have a table that lists standard lumber sizes, and in the next column for each size I need a formula that will lookup the quantities needed for the given beam length.
I started with "=vlookup(beamsize,tableA,2,false)+vlookup(beamsize,tableA,4,false)
But I cannot distinguish between the sizes in that scenario. For example, if I input that formula into each of the 4 cells in the qty column of tableB, I would end up with a qty of 1 of each size for a beam length of 8-16. And then I would end up with 2 of each for beam length 17 & 18.
What I should see in tableB for beam length 8 is onw 2x12x8' and zero others. For a beam length of 17' I should see one 2x12x8' and one 2x12x10'. And for a beam length of 43' I should see one 2x12x12' and two 2x12x16'.

How do I get the lookup to return the size1 and size2 info as well as the qty1 and qty2? And how do I inject those into the correct cells in tableB?

Or... is there a better way to do this instead of a lookup table?






Table A Table B

length qty1 size1 qty2 size 2 lumber qty
8 1 8 2x12x8'
9 1 10 2x12x10'
10 1 10 2x12x12'
11 1 12 2x12x16'
12 1 12
13 1 16
14 1 16
15 1 16
16 1 16
17 1 8 1 10
18 1 8 1 10
.
.
.
43 1 12 2 16

etc

[Fotis1991]

Can you upload a small sample workbook?

[rfcomm2k]

I have tried to upload a 16K sample but it will not let me. I am having the same problem with this same BBS software on an architectural website, so IDK if I have trouble or if there is a bug with the BBS software.

I see that the sample data I shared does not look very good so let me try here to separate it. BTW, the qty values in tableB were derived from entering a beam size of 8'



Table A
length qty1 size qty2 size
8 1 8
9 1 10
10 1 10
11 1 12
12 1 12
13 1 16
14 1 16
15 1 16
16 1 16
17 1 8 1 10
18 1 8 1 10
19 1 8 1 12


41 1 10 2 16
42 1 10 2 16
43 1 12 2 16
44 1 12 2 16


Table B
Lumber qty
2x12x8' 1
2x12x10' 1
2x12x12' 1
2x12x16' 1