Is it possible to use a VLOOKUP with an array for the lookup value

[DashboardNovice]

In the attached file, my formula is in BG8. The final formula will be a SUMIFS, but for now I want to break it down to only one criteria (the one that's giving me trouble) before I introduce the second criteria.

The pay periods in yellow constitute the criteria range. The criteria is the month ending dates in orange. Obviously the criteria range does not contain month ending dates so I tried using a VLOOKUP on that criteria range using the yellow cells as the look up value, and the table array for that VLOOKUP is on the tab called Lookup Tables.

So I was hoping that I could somehow do a VLOOKUP on the yellow cells and return the corresponding value (in memory in an array). By doing that, now the SUMIF criteria range contains a range of values (month ending dates), which means that a match can be found when using the orange cells as a lookup.

So I tried using VLOOKUP with the yellow cells as the lookup value, and pressed Ctrl + Shift + Enter and this did not work.

I know I could have a VLOOKUP on row 5 that returns the month ending date for each pay period, but I would like to be able to do this, in a formula.

Is this possible? Is an array the best solution? I feel like there might be another solution but nothing is coming to mind.

 

[Chihiro]

There's probably better solution. But this should do what you are looking for.

Since there can be maximum of 3 matching values that should be returned for each end of month. You can use =SUM(IF()) array formula to add values that meet criteria.
https://support.microsoft.com/en-us/kb/275165

You can use INDEX(Result Array,Small(IF(Lookup Array=Value,Row Index - MIN(Row Index)+1,""),ROW(A1))) array formula to return array of matching values.
http://thinketg.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/

Combining 2 concepts... resulting formula is going to be
=SUM(IF(($B$6:$BA$6=INDEX('Lookup Tables'!$E$2:$E$43,SMALL(IF('Lookup Tables'!$G$2:$G$43=Withholdings!BG$6,ROW($G$2:$G$43)-1,""),1)))+($B$6:$BA$6=INDEX('Lookup Tables'!$E$2:$E$43,SMALL(IF('Lookup Tables'!$G$2:$G$43=Withholdings!BG$6,ROW($G$2:$G$43)-1,""),2)))+($B$6:$BA$6=INDEX('Lookup Tables'!$E$2:$E$43,SMALL(IF('Lookup Tables'!$G$2:$G$43=Withholdings!BG$6,ROW($G$2:$G$43)-1,""),3))),$B$8:$BA$8,0))

But, since there are instances where 3rd occurrence will result in error... modify formula to be.
=SUM(IF(($B$6:$BA$6=INDEX('Lookup Tables'!$E$2:$E$43,SMALL(IF('Lookup Tables'!$G$2:$G$43=Withholdings!BG$6,ROW($G$2:$G$43)-1,""),1)))+($B$6:$BA$6=INDEX('Lookup Tables'!$E$2:$E$43,SMALL(IF('Lookup Tables'!$G$2:$G$43=Withholdings!BG$6,ROW($G$2:$G$43)-1,""),2)))+($B$6:$BA$6=IFERROR(INDEX('Lookup Tables'!$E$2:$E$43,SMALL(IF('Lookup Tables'!$G$2:$G$43=Withholdings!BG$6,ROW($G$2:$G$43)-1,""),3)),"")),$B$8:$BA$8,0))

Entered as array (CSE) in BG8.

See attached.

 

[r1c1]

@DashboardNovice

There is a chance that I misunderstood your question. As there are no numbers in your workbook, I tried to guess what you wanted.

So if you want to find out the sum of all Actual amounts for a particular employee in a given month end date (ie all pay periods in that month), here is an array formula that should work. Press Ctrl+Shift+Enter to get it work.

=SUM( SUMIFS(B8:BA8, $B$7:$BA$7, BG$7, $B$6:$BA$6, TRANSPOSE(IF(BG6='Lookup Tables'!$G$2:$G$43,'Lookup Tables'!$E$2:$E$43))))


Essentially, we pass a range of pay periods as criteria in the SUMIFS and then use SUM to sum up all those values.

Since your mapping table is vertical where as your SUMIFS is running against values spread horizontally, I am using TRANSPOSE to change the orientation.

 

[Chihiro]

@r1c1
Such an elegant solution. Love this forum. I learn something new almost everyday. Transpose is something I've never used before, but this gives me some ideas.

 

[r1c1]

@Chihiro... Thank you. It is a pleasure to have you here on Chandoo.org forum and learn from you. Keep up the good work