Sum results from multiple lookups in 1 formula

buzza

New Member
Joined
Apr 9, 2014
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I have a number of tables and I want to use a formula to lookup a value from each table depending on what has been selected in another cell. for example, A1 has "2" in it and B1 has "4".

I want to lookup "2" (from A1) in range / table A which will return £50, then I want to lookup "4" (from B1) in range / table B which will return £100. I then want the formula to add these values together and give me £150.

Is that possible? I have tried a number of formulas using vlookup with no success. Basically =vlookup()+vlookup(). It seems you can't have multiple vlookups unless nested though.

There may end up being 10+ values to look up in their own range and added together.

The workaround I came up with is to do the lookups on another sheet and then use a simple =sum to add them together. I would be happy with this method but also interested if it can be done in a single formula.

thanks.

Edit: I am basically trying to build a configurator if anyone knows of a good template?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
This should work.
So I just mocked up a quick sheet and yes it does indeed work.

However, my values in A1 & B1 are a data validation dropdown list from the table range. As soon as text is in the table it does not work! See below. E15 has text in so formula is not working.
1652891048417.png
 
Upvote 0
Is this for Excel, or Google Sheets?
 
Upvote 0
Is this for Excel, or Google Sheets?
It happens in both.

The finished document will be excel but I have been working on it on google sheets as it is easier to work on it both at home and at work for me. I can solely use excel if GS is hindering me somehow.
 
Upvote 0
As soon as text is in the table it does not work!
That's got nothing to do with the fact it's text, it's because you are looking for 1 in the range & it does not exist.
 
Upvote 0
That's got nothing to do with the fact it's text, it's because you are looking for 1 in the range & it does not exist.
excelsample.xlsx
ABCDEFGHI
11 - text4
2
31 - text10
4220
5330
6440
7
8
9total110
10#N/A220
11330
12440
Sheet1
Cell Formulas
RangeFormula
A10A10=VLOOKUP(A1,H9:I12,2,FALSE)+VLOOKUP(B1,H3:I6,2,FALSE)
Cells with Data Validation
CellAllowCriteria
A1List=$H$3:$H$6
B1List=$H$9:$H$12


see for yourself. A1 is a dropdown list from H3:H6. the total is not added. remove the -text from H3 and reselect 1 in cell A1. the total is now added.
 
Upvote 0
also, if you remove the first vlookup it will return the correct value next to "1 - text"
 
Upvote 0
You get the error because "1 - text" does not exist in in H9:H12, therefore the vlookup returns #N/A
 
Upvote 0
That's got nothing to do with the fact it's text, it's because you are looking for 1 in the range & it does not exist.

You get the error because "1 - text" does not exist in in H9:H12, therefore the vlookup returns #N/A
ah yes, I have my drop down range looking at the wrong table ?‍♂️

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top