VLOOKUP formula for multiple criteria without helpwe column

[Wafee]

Hi, I have below formula where it initially checks if B is empty and if it is empty it does a vlookup with values in respective "A" column.

First vlookup is working as expected but need help with 2nd vlookup which is highlighed in red.

for 2nd vlookup i need formula that checks values in both column A and B in both the sheets without including any helper column and comes up with a value from the grand total column.



IF($B4= "",VLOOKUP($A4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0),VLOOKUP($B4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0))

 

[ekrause]

I took a stab at what I think your asking

IF($B4= "",VLOOKUP($A4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0),VLOOKUP($A4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0)+VLOOKUP($B4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0))

Would you be able to to post an example of what you are looking for? I'm not sure I fully understand what you're asking.

 

[Wafee]

I took a stab at what I think your asking

IF($B4= "",VLOOKUP($A4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0),VLOOKUP($A4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0)+VLOOKUP($B4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AZ$2,0),0))

Would you be able to to post an example of what you are looking for? I'm not sure I fully understand what you're asking.

Hi ekrause,

let us say below is my outputsheet and formula will e put in column "C"

Book4
	A	B	C
1	Name 1	Name 2	Grand Total
2	Hari
3	Hari	Sam
4	Hari	Blanks
Sheet1

and here is my source sheet.

Book4
	F	G	H
1	Name 1	Name 2	Grand Total
2	Hari		40
3	Hari	Sam	30
4	Hari	Blanks	20
5	Murat	Sam	10
Sheet1

so for the first name in A2 as B2 is empty it is enough to look for "Hari" and pull the amount. but for nect two cellls it should take a combination of both (A3 & B3) and then should go and check same in the put (A Column data & B Column data ) and then match the grand total column and pull the respective value.

 

[ekrause]

Use a Sumifs formula =SUMIFS($H$2:$H$5,$F$2:$F$5,A2,$G$2:$G$5,B2)

If that doesn't work use a sumproduct formula. These formulas can get time intensive if you are using big data sets.

I wrote the formula based on the screen shots above

=SUMPRODUCT($H$2:$H$5,($F$2:$F$5=A2)*1,($G$2:$G$5=B2)*1)