Sum results from multiple lookups in 1 formula

[buzza]

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?

 

[Fluff]

=vlookup()+vlookup()

This should work.

 

[buzza]

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.

 

[Fluff]

Is this for Excel, or Google Sheets?

 

[buzza]

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.

 

[Fluff]

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.

 

[buzza]

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
	A	B	C	D	E	F	G	H	I
1	1 - text	4
2
3								1 - text	10
4								2	20
5								3	30
6								4	40
7
8
9	total							1	10
10	#N/A							2	20
11								3	30
12								4	40
Sheet1

Cell Formulas
Range	Formula
A10	A10	=VLOOKUP(A1,H9:I12,2,FALSE)+VLOOKUP(B1,H3:I6,2,FALSE)

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$H$3:$H$6
B1	List	=$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.

 

[buzza]

also, if you remove the first vlookup it will return the correct value next to "1 - text"

 

[Fluff]

You get the error because "1 - text" does not exist in in H9:H12, therefore the vlookup returns #N/A

 

[buzza]

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