Conditional VLOOKUP?

[brooklyn2007]

Hello guys. I have this question. Let say cell A1 is John, cell B1 is White, cell C1 is 235. Also lets say cell A2 is also John, cell B2 is Brown, cell C2 is 124.

I need a VLOOKUP formula that would look the value for the condition in the range A to be John and range B to be White. In this case the right result would be 235.

I have a large set of data and each of them repeat twice in the range A with the same name, but different names in range B. In range B I have cells with only two different names which are called (lets say) either X or Y. Range C has different values. I want to make a selection for what kind of data I'm looking for.

Thanks

 

[Aladin Akyurek]

Hello guys. I have this question. Let say cell A1 is John, cell B1 is White, cell C1 is 235. Also lets say cell A2 is also John, cell B2 is Brown, cell C2 is 124.

I need a VLOOKUP formula that would look the value for the condition in the range A to be John and range B to be White. In this case the right result would be 235.

I have a large set of data and each of them repeat twice in the range A with the same name, but different names in range B. In range B I have cells with only two different names which are called (lets say) either X or Y. Range C has different values. I want to make a selection for what kind of data I'm looking for.

Thanks

E1: John

F1: White

G1, control+shift+enter, not just enter:

Either...

=INDEX($C$1:$C$100,MATCH(1,IF($A$1:$A$100=E1,IF($B$1:$B$100,1)),0))

Or...

=INDEX($C$1:$C$100,MATCH(F1,IF($A$1:$A$100=E1,$B1:$B$100,0),0))

 

[cgcamal]

Hi brooklyn2007,

If the structure is as below, maybe the following formula in G2 works for you:

Excel Workbook
	A	B	C	D	E	F	G
1	John	White	235		Name to look up	Last Name	Result
2	John	Brown	124		John	White	235
3
4
...

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(($A$1:$A$20=$E$2)*($B$1:$B$20=$F$2)*$C$1:$C$20)

Hope this helps.

Regards.

 

[brooklyn2007]

Thanks guys for you help . I think the SUMPRODUCT formula will be better since it does not kill my pc proccesor compared to the INDEX & MATCHING

 

[cgcamal]

You're welcome. Nice to know it helps you.

Regards.

 

[Aladin Akyurek]

Thanks guys for you help . I think the SUMPRODUCT formula will be better since it does not kill my pc proccesor compared to the INDEX & MATCHING

How do you mean? Also, if there are multiple co-occurrences, SumProduct won't give you the right answer unless you want to sum.