Excel look up help

mattyfez

mattyfez

mattyfez

Soldato
Joined
12 Apr 2007
Posts
12,527
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
 
mattyfez said:
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
Click to expand...


You shouldn't have any more than one '=' in a full formula. Remove the second and third one and it should work.
 
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:
Hi Mattyfez,

=LOOKUP is kind of an older way of doing it which is only in the newer versions of excel for compatibility.

Usually you should be using =VLOOKUP or =HLOOKUP.

VLOOKUP finds a value in the left most column of an area, and then gives you a result from another column on the same row (This is what you want)

HLOOKUP is the same but kind of the other way around, it looks up a result on the top row, and then finds a result on the same column but in a different row.

For VLOOKUP and HLOOKUP you can specify if it has to be an "exact match" this is so you don't have to sort your table. I think =LOOKUP may require you to put things in alphabetical order, but either way it's an old way of doing it.

So.. you might want to change your formula to:

VLOOKUP("Apples",A1:B3,2,FALSE)

This says...
Find Apples in the first column of the range A1:B3

The "2" says then get the result from the second column of that range.
False then means find an exact match only. So if they are not in order this will search through all of your values. If they are in order you may use TRUE at the end, but it may not return what you expect. This would find the closest word to the "Apples" alphabetically and then return the result for that word, but it expects the list to be sorted.

In the excel document I linked to,.. you can change the formula to:
=VLOOKUP("Apples",A1:B3,2,FALSE) +VLOOKUP("Apples",E1:F3,2,FALSE)

Hopefully you can adapt this to what you're trying to do.

For you it looks like it will be:

=VLOOKUP("apples",W4:X18, 2, FALSE)+VLOOKUP("apples",W23:X37, 2, FALSE)

If you had for example a column between "Apples" and the value... so maybe you had three columns:
FRUIT COLOUR VALUE

Then you could change the "2" to a "3" to say get the result from the third column rather than the second. That's one reason why it's better than the old LOOKUP() function
 
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!!!!
 
No probs,

Formulas shouldn't be a problem like that. Part of the reason we use Excel is so that things can automatically update. It's ok to have a formula relying on a cell which is calculated itself.

What actually happens with the formula? Does it say some kind of error?

Also are the cells formatted as integers?

Is it something you can send? I have sent you a trust message with my email address in, if you can send it ( I don't know what it contains / if you can or can't ),.. then if you send it over to me i'll take a look.

Thanks,
 
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