Two seperate Columns, 1 reference

[Audiguy82]

I am having difficulty getting 2 seperate cells to reference a single cell for different results. I tried using IF, but only being able to have 7 conditions was too limiting. I also tried using LOOKUP, but the fact that the tables have to be alphabetical and in numerical order doesnt work. I will give an example


Apple 5 10
banana 1 8
Zebra 2 12

That is my "data" how do I make it so that if i type banana in the reference cell, one column becomes a 1, and another becomes a 8. Thanks in advance.

[jasoncw]

Do you mean typing in "banana" returns 1 and 8? That would make more sense, since they are in the same row as banana. If that is what you want, you can use SUMPRODUCT.

Assuming that table you listed below is in cells A1:C3, and the "data" is in E1, the two formulas to insert in F1 and G1 are:

=SUMPRODUCT((A1:A3=E1)*(B1:B3))

and

=SUMPRODUCT((A1:A3=E1)*(C1:C3))

HTH

Jason

[Audiguy82]

What I meant was I have a huge table with three columns like such

Name Value 1 Value 2, for example

banana 2 5
apple 1 7 etc.


then elsewhere on the page, I want several cells to reference this table, so when I type banana the other two cells become 2 and 5, or had i typed apple then the two cells become 1 and 7. I hope that clears it up a little. And thanks for the quick response btw

p.s. or was that what you meant with your response and sumsproduct will work?

[Audiguy82]

I just tried out your sum product and i think its gonna work. Thank you soo much man!! I've been racking my brain trying to get IF or LOOKUP to work. You rock man!!

[jasoncw]

Yes, that is how I used the SUMPRODUCT function above. Glad you got it working!

Jason