Summing the results of multiple VLOOKUP's

Your syntax looks correct, and the fact that the lookup value is the result of a function should not matter (see example).

In order to debug this, we will probably need a more detailed description of what is going on. What error or wrong answer are you getting from this? Are you certain the values are numbers (and not numbers stored as text)? Recognize that functions like LEFT(), MID(), RIGHT() and other text functions return text strings, even if the result looks like a number.

A sample spreadsheet may be invaluable to help explain your problem.

In the absence of any data (that is, a sample workbook), your guess is as good as anyone's.

Note you have an excess of brackets to no purpose.

Regards, TMS

As noted in the help files (https://support.office.com/en-us/art...__toc309306716 ), the average and sum functions ignore text. This includes "numbers stored as text" You haven't said anything about the formulas used to generate these values, but if these formulas generate them as "text strings" (examples: LEFT(), RIGHT(), MID() functions -- follow above link to also see a list of Excel's text functions), the the average() and SUM() functions will ignore them -- yielding a #Div/0# error if all of the values are "numbers stored as text".

If this is the case, you need to modify your text formulas to force them to return numbers. This can be done using the =VALUE() function or by adding 0 =text function +0 or by multiplying by 1 or other suitable strategy.

You're welcome. Thanks for the rep.

Any ideas!

Yes, upload some data so we can see what you're working with. And please, don't post numbers in the thread, post a sample workbook.

Let's say you had 12345 in a cell as a number. =LEFT($A$1,3), =MID($A$1,2,3), =RIGHT($A$1,3), would all return text strings

=--LEFT($A$1,3), =--MID($A$1,2,3), =--RIGHT($A$1,3), would all return numbers
=LEFT($A$1,3)+0, =MID($A$1,2,3)+0, =RIGHT($A$1,3)+0, would all return numbers
=LEFT($A$1,3)-0, =MID($A$1,2,3)-0, =RIGHT($A$1,3)-0, would all return numbers
=LEFT($A$1,3)*1, =MID($A$1,2,3)*1, =RIGHT($A$1,3)*1, would all return numbers
=LEFT($A$1,3)/1, =MID($A$1,2,3)/1, =RIGHT($A$1,3)/1, would all return numbers


Regards, TMS