Excel Formula

[Guest]

I have been trying to calculate some cells based on criteria located in
several other locations.
vlookup range name = rangecorrection
For example, trying to return a value for cell C5. However the information
is in worksheet "Correction Values" and will require the formula to search
"BAAA 2.0" in column Aof the above mentioned worksheet as well as "TS" in
column B in order to return the value (see below for example)
currently have formula:
=VLOOKUP(B4,rangecorrection,3,FALSE)&VLOOKUP(C4,rangecorrection,3,FALSE)
Column A Column B Column C
BAAA 2.0 TS
Row 1 .................. 130 #N/A
Row 2 ................... 230 #N/A

 

[SteveG]

So you want to look at column A in sheet Correction Values and fin
"BAAA 2.0" and column B in sheet Correction Values and find "TS" and i
both conditions are met then return the value from column C i
Correction Values?

Try,

=SUMPRODUCT((CorrectionValues!A1:A?="BAA
2.0")*(CorrectionValues!B1:B?="TS")*(CorrectionValues C1:C?))

This will sum the values in CorrectionValues!C1:C? so long as the tw
conditions are met in columns A & B. If there is only one occurence i
will only return that one value. The size of your search ranges need t
be the same (i.e. A1:A10, B1:B10, C1:C10) You could name each colum
range if you prefer using named ranges vs. cell references.

Does that help?

Stev

 

[Guest]

I tried the calculation, but it gives me a #NUM! error.
"BAAA 2.0" is a factor that could change to "BCCC 2.4" and "TS" could change
to "CE"...

this is what I put in
=SUMPRODUCT((CorrectionValues!A:A=C4)*(CorrectionValues!B:B=D4)*('Correction
Values'!C:C))

Any ideas?

 

[Peo Sjoblom]

You can't use the whole column in an array formula or sumproduct working as
an array formula, change the A:A to A1:A10000 or whatever and the same for
B:B, that's the reason Steve put a wildcard there in his response

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

The value is now returning a "0"... should be returning 25 which is in Column
C for BAAA 2.0 TS

=SUMPRODUCT('Correction Values'!A2:A65536=C4)*('Correction
Values'!B2:B65536=D4)*('Correction Values'!C2:C65536)

 

[Guest]

Disregard below... I was missing a few brackets.
thanks a million for your help

 

[SteveG]

Correna,

Glad you got it with some help from Peo. I went offline yesterday so
sorry for the late reply.

Regards,

Steve