# Thread: Picking Values from a Table Thanks: 0 Likes: 0

1. ## Picking Values from a Table

Hi All

I am inputting values into row 2 under certain labels. Each label has a value which corresponds with the values in table A5:B9. The total is then shown in cell B12 (product of a multiplication).

I am trying to remove the intermediate step which requires table D12:E16. I assume a MATCH / INDEX / HLOOKUP or SUMPRODUCT formula may be the answer but I'm not sure.

There are only 5 labels shown here but I need the flexibility to expand.

Thanks

ABCDE
1ABCDEFGHIJKLMNO
220506
3
4
5ABC16.1
6DEF12.8
7GHI15.4
8JKL11.4
9MNO12.5
10
11
12Total184.2ABC32.2
13DEF0.0
14GHI77.0
15JKL0.0
16MNO75.0

Sheet2

Worksheet Formulas
CellFormula
B12=SUM(E12:E16)
E12=A2*B5
E13=B2*B6
E14=C2*B7
E15=D2*B8
E16=E2*B9  Reply With Quote

2. ## Re: Picking Values from a Table

=SUMPRODUCT((A1:E1=A5:A9)*(A2:E2)*(B5:B9))  Reply With Quote

3. ## Re: Picking Values from a Table

Hey thanks very much Fluff. Works a breeze.  Reply With Quote

4. ## Re: Picking Values from a Table

You're welcome & thanks for the feedback  Reply With Quote

5. ## Re: Picking Values from a Table

Hi Fluff

I've found one small issue. If the values in B5:B9 are arrived at using a formula, I get the #Value ! error.

Is there a way around this so that I can use the values that are from a formula ?

Thanks  Reply With Quote

6. ## Re: Picking Values from a Table

The fact that you have formulae in B5:B9 shouldn't make any difference, unless one or more of them is returning #VALUE ! or "", in which case you need to change that formula so that it returns 0  Reply With Quote

7. ## Re: Picking Values from a Table

Correct again Fluff. I did have an IF statement that returned a "" if true. I have now changed this to return 0.

All works fine again thanks.  Reply With Quote

8. ## Re: Picking Values from a Table

Glad to help & thanks for the feedback  Reply With Quote

## User Tag List

formula, labels, shown, table, values 