unexpected result on average of 2 vlookup

"daredelsol" wrote in message
...
Hello. I have two VLOOKUP formulas that give the right answer in two
cells and then I can get an average of those two cells correctly by
using AVERAGE. But, when I try to combine the two VLOOKUPs into one
cell's AVERAGE formula I get an unexpected result.

The area in the VLOOKUPs appear to not change when I am on the
individual formula as shown he

VLOOKUP(M2,TableData,6,FALSE) = 26
VLOOKUP(M2,TableData,7,FALSE) = 46
Average of both: AVERAGE(I3:I4) = 36 correct

AVERAGE(VLOOKUP(M2,TableData,6,FALSE),(VLOOKUP(M2, TableData,7,FALSE)) =
19 incorrect

Why? and What is wrong? Thanks!!!


I cannot reproduce your problem exactly. There would be a difference between
your two scenarios if one of the values returned from the vlookups was text
rather than a number (the number 26 is not the same as the text "26"), as
AVERAGE(I3:I4) would not convert text to a number whereas
AVERAGE(VLOOKUP...,VLOOKUP...) would convert it. But unless your example is
a simplification of the actual problem, this would not give a result of 19.

Perhaps you have a typing error? I notice that you have 4 left brackets but
only 3 right ones in the composite formula as quoted.

What you can do is to look at the results of part of a formula in the
formula bar. Select the cell containing the composite formula.
Now in the formula bar, select
VLOOKUP(M2,TableData,6,FALSE)
and press F9. You will see the result replace this part of the formula in
the formula bar, so you can check that it is 26. Press ESC to get out of
this mode. Repeat for
VLOOKUP(M2,TableData,7,FALSE)
to check that it is 46.
This may help you locate the problem.