Double vlookup/sumifs

[ACHartz]

Hello,

Apologies if this is basic or has already been answered, but I'm stuck on getting a formula correct. In essence, I have a sheet with:
1. A tab (Tab1) with information entered for each month
Month Sales Cost
Jan-18 45 3
Feb-18 22 1
etc
2. Anther tab (Tab2) listing each text month and the corresponding date
Jan Jan-18
Feb Feb-18
etc
3. A final tab where the user enters the month and a vlookup/match pulls that month's information
Month: Jan
Sales: =VLOOKUP(VLOOKUP((B1,Tab2!A1:B12,2,0),Tab1!A1:C12,Match(A2,Tab1!A1:A12,0),0)
Cost: =VLOOKUP(VLOOKUP((B1,Tab2!A1:B12,2,0),Tab1!A1:C12,Match(A3,Tab1!A1:A12,0),0)
etc

My current formula works. What I would like to do is add the ability to indicate either a month or a quarter. I have added the below to Tab2
Q1 Jan-18
Q1 Feb-18
Q1 Mar-18
Q2 Apr-18
etc

But I'm now stuck on how to change my vlookup to allow for multiple results. I assume I need to change the formula to a sumif or sumifs, but I don't know how to incorporate that with the vlookup to tell the formula what months are associated with Q1.

Thank you in advance for your help! I hope that the formatting looks OK, I can't download any of the applications that would allow me to post screenshots.

 

[Col Delane]

Re: Help with double vlookup/sumifs

Hi AC & welcome to the Forum

Having Tab2 as an intermediate lookup / translation table is a bit clunky, and the resulting double vlookup adds unnecessary calculation overhead and time.

My initial suggestion would be to expand Tab1 (where your "real" data is held) to look like this:
[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]Month_Date
[/TD]
[TD]Month_Txt
[/TD]
[TD]Qtr
[/TD]
[TD]Sales
[/TD]
[TD]Cost
[/TD]
[/TR]
[TR]
[TD]Jan-18
[/TD]
[TD]Jan
[/TD]
[TD]Q1
[/TD]
[TD]45
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Feb-18
[/TD]
[TD]Feb
[/TD]
[TD]Q1
[/TD]
[TD]22
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]



The values (text strings) Month_Txt and Qtr can be returned by formula from Month_Date.

Your formulas in Tab3 then become:
Lookup using Month_Txt
Sales: = IFERROR( INDEX( Tab1!$D$1:$D$12, Match(A2, Tab1!$B$1:$B$12, 0) ), 0)
Cost: = IFERROR( INDEX( Tab1!$E$1:$E$12, Match(A2, Tab1!$B$1:$B$12, 0) ), 0)

Lookup using Qtr
Sales: = IFERROR( INDEX( Tab1!$D$1:$D$12, Match(A2, Tab1!$C$1:$C$12, 0) ), 0)
Cost: = IFERROR( INDEX( Tab1!$E$1:$E$12, Match(A2, Tab1!$C$1:$C$12, 0) ), 0)

And if you want to be able to enter either Month_Txt or Qtr in the same lookup_value cell (Tab3!A2, etc.), one way is:
Sales = IFERROR( INDEX( Tab1!$D$1:$D$12, Match(A2, Tab1!$B$1:$B$12, 0) ), 0) + IFERROR( INDEX( Tab1!$D$1:$D$12, Match(A2, Tab1!$C$1:$C$12, 0) ), 0)
Cost = IFERROR( INDEX( Tab1!$D$1:$D$12, Match(A2, Tab1!$B$1:$B$12, 0) ), 0) + IFERROR( INDEX( Tab1!$D$1:$D$12, Match(A2, Tab1!$C$1:$C$12, 0) ), 0)

Only one leg of each formula will find a match & return the corresponding value, with the non-matching INDEX/MATCH producing a #N/A error which IFERROR will substitute with zero.

Note that the above (and your original formulas) does NOT allow for data for multiple years (e.g. Jan-18 and Jan-19) as the formula do not know which year-month combo you're seeking! A better overall solution may be to ensure that only date number values (which match those in Tab1!$A$1:$A$12) can be entered in Tab3!A2, etc. . (this can be implemented via Data Validation) and then just look for that value in Tab1!$A$1:$A$12.

HTH

Cheers

 

[ACHartz]

Re: Help with double vlookup/sumifs

Thank you Col! I had been trying to keep Tab1 as clean as I could since both Tab1 and Tab3 are presentation sheets, but I can just add some hidden columns to throw in the month and quarter and your formulas return what I'm looking for.

In trying to keep the sheet as flexible as possible though (since I will likely need to add more categories than just Sales and Cost), I'd like to have the formula reference A2 (Sales) or A3 (Cost) to determine which column in Tab1 to sum (instead of just Tab1!$D$1:$D$12 or Tab1!$E$1:$E$12). I was thinking another Index Match would work, like

=IFERROR(INDEX(INDEX(Tab1!$D$1:$E$12),,MATCH(A2,Tab1!$D$1:$E$1)...

But that isn't working, I think because it doesn't return the whole column that I want the rest of the formula to use. Is there a way to get that to work?

 

[Col Delane]

Re: Help with double vlookup/sumifs

Rather than an INDEX\INDEX\MATCH combo you need an INDEX\MATCH\MATCH that does this: INDEX( Range of Values to return, row position of Month, column position of value type)

Using the table in my post, where the column headings are in row 1 and first data row (Jan-18) is row 2:
=IFERROR( INDEX( Tab1!$D$1:$E$12, Match( [cell where Mth or Qtr is selected], Tab1!$B$1:$B$12, 0), Match([cell where Sales or Cost is selected], Tab1!$A$1:$E$1, 0)), 0)

Note that the first argument for the INDEX function now covers both Sales $ and Cost $ (i.e. two columns rather than just one.) and the MATCH's instruct it which row and column to return the value from.

 

[Aladin Akyurek]

Re: Help with double vlookup/sumifs

Book1
	A	B	C
1	Month	Sales	Cost
2	Jan-18	45	3
3	Feb-18	22	1
Sheet1

Book1
	A	B
1	ticker	month/year
2	Q1	Jan-18
3	Q1	Feb-18
4	Q1	Mar-18
5	Q2	Apr-18
6	Jan	Jan-18
7	Feb	Feb-18
Sheet2

Book1
	A	B	C
1	month/quarter	sales	cost
2	jan	45	3
3	Q1	67	4
Sheet3

In B2 control+shift+enter, not just enter, copy across, and down:

=SUM(SUMIFS(INDEX(Sheet1!$B:$C,0,MATCH(B$1,INDEX(Sheet1!$B:$C,1,0),0)),Sheet1!$A:$A,IF(Sheet2!$A$2:$A$20=$A2,Sheet2!$B$2:$B$20)))

 

[ACHartz]

Re: Help with double vlookup/sumifs

Thank you both so much, this is perfect!

 

[Aladin Akyurek]

Re: Help with double vlookup/sumifs

You are welcome.

 

[Col Delane]

What he (Aladin) said!!