Nested Vlookup

[ChrisInphoM]

I am trying to build a function that will look for 3 separate values in the same row, and if they all match, display a different column in that matching row.

I have been trying to use Nested VLOOKUP commands but am not able to get it working.

Below is an example of the data I am trying to use. (There are 4 Columns)

Alltel Alltel 0 10,906
Alltel AT&T 0 1,270,022
Alltel CBW 0 2,270
Alltel Cricket 0 143,810
Alltel Sprint 0 678,433
Alltel T-Mobile 0 547,134
Alltel Alltel 1 20,640
Alltel AT&T 1 791,268
Alltel CBW 1 1,399
Alltel Cricket 1 113,386
Alltel Sprint 1 436,989
Alltel T-Mobile 1 375,586
Alltel Verizon 1 914,387

As an example, I would like to be able to have a function like this..

If Column A=Alltel and Column B=AT&T and Column C=1 then display Column D

Any help is greatly appreciated

[martindwilson]

=SUMPRODUCT(--($A$1:$A$13="alltel"),--($B$1:$B$13="at&t"),--($C$1:$C$13=1),($D$1:$D$13))D1:D13))
will work unles you have two or more rows where info in a.b.c is the same then the result will be the total of those rows.
you can replace text with cell ref e.g.
=SUMPRODUCT(--($A$1:$A$13=E1),--($B$1:$B$13=F1),--($C$1:$C$13=G1),($D$1:$D$13)) where e1 ,f1 and g1 hold text of choice

[ChrisInphoM]

Thank you for the response. I tried your suggestion and received an error offerring to fix the formula...

Here is the suggested change

=SUMPRODUCT(--($A$1:$A$13="alltel"),--($B$1:$B$13="at&t"),--($C$1:$C$13=1),($D$1:$D$13))*D1:D13

[martindwilson]

=SUMPRODUCT(--($A$1:$A$13="alltel"),--($B$1:$B$13="at&t"),--($C$1:$C$13=1),($D$1:$D$13))D1:D13)) is a typo should be
=SUMPRODUCT(--($A$1:$A$13="alltel"),--($B$1:$B$13="at&t"),--($C$1:$C$13=1),($D$1:$D$13)) disregard suggested change!!!!!!!!! its wrong

[ChrisInphoM]

Works Great!!!

Thank You very much for the help.

[ChrisInphoM]

Am I able to modify the $A$1:$A$13 statement, so that it will look at the entire Column?

THis is because the source data will be generated daily (that the evaluation is then applied to) and may contain different numbers of rows.

[JBeaucaire]

Or even:
G2 = "Alltel"
H2 = "AT&T"
I2 = 1

Data as displayed in post 1 in columns A:D

=INDEX($D$1:$D$1000, MATCH(G2 & H2 & I2, INDEX($A$1:$A$1000 & $B$1:$B$1000 & $C$1:$C$1000, 0), 0))

[martindwilson]

you know what i tried adding the three index ranges together a few days ago and kept getting error so i assumed you couldnt add more that 2 i must have mistyped something!!!!
but in reply
no sumproduct must have a definite range and all must be the same length a1:a6000,b1:b6000
(pre excel 2007) but you can make it big!!! eg a1:a5000
both methods re different the index/ match will only return first value ,the sum product will add any of them it finds

[AllanNWSPP]

I realize this is an old thread but my query is an expansion on the original question.

Using the OP's data as an example, where his criterion was "If Column A=Alltel and Column B=AT&T and Column C=1 then display Column D", what would need to be added/changed to the formula to accommodate multiple results and sum them?

i.e. "Alltell AT&T 1 1000"
"Alltell AT&T 1 2000"
"Alltell AT&T 1 3000"

display "6000" in column D

Using the original formula only gives me the first result found, which using the above data would be 1000.

[JBeaucaire]

See the second formula offered in post #4.