Excel look up help

mattyfez

mattyfez

mattyfez

Soldato
Joined
12 Apr 2007
Posts
12,719
Hi all,

Hope someone can help with this - im trying to create a formula which takes the results of 2 lookup formulas and adds them together.

For example if I have 2 tables, which are not in the same order, I want to add 2 values and display the result in a third table.

A ......................B
1Apples.............10
2Oranges...........7
3Pears...............5


E ......................F
1Pears...............20
2Apples..............15
3Oranges............10


So what i want to do is:

=LOOKUP("apples",A1:B3) which would result in 10

Plus

=LOOKUP("apples",E1:F3) which would result in 15


So in the third table, Apples would result in 25

Is there a way to do this? it's breaking my head!!

Thanks
 
Thanks but still struggling!

This is what im trying to do but it wont let me :(

=SUM((=LOOKUP("apples",W23:X37))+(=LOOKUP("apples",W4:X18)))

Im using LOOKUP as both source tables are sorted by 'number of fruit present' in descending order, which varies, if that makes sense.

Thanks again
 
Thanks,

I think im getting warmer its now accepting the formula:

=LOOKUP("apples",W4:X18)+LOOKUP("apples",W23:X37)

But it comes up with the wrong result

the two values are 243 and 109 for apples, the result should be 352, but its showing as 166

Does it make any difference that 'apples' is not always in the same position in the 2nd table, hence the LOOKUP?
As in, from your example if you change the second table, 'apples' in E2 with a value of 15, and oranges in E1 with a value of 20, can I still add them up?

I'm quite new to this :S

Thanks again
 
Last edited:
Thanks for taking the time to explain :)

Still not working though, although i think I might know why..

The values in the source tables are not numerical, they are actualy formulas pulling results from elswhere in the spreadsheet, so instead of one of the source values being for example, 10....

Its actually some thing like =A6-B6 , which is 16 & 6 respectivley, equaling 10.

Grrrrrr!!!!
 
Wow thanks,

ill send email in a sec - basicaly apples in column AA needs to be the sum of apples in the range:
X4 to X18, and X23 to X37

But the location of apples in these ranges will change, as this is based on a formula, which in turn is based on formulas lol!

Ill mail it over now, it looks a bit funny as ive had to cut out a bunch of info
 
You must log in or register to reply here.
Back
Top Bottom