Vlookup and match on 2 criteria

[Melimob]

Hi there

I have a an sheet where the sales person (B3) and year (A2) is a drop down so when selected, can dynamically change the values in my output table.
My output table has the months as columns JAN-DEC.
Year is not included in the table
Lets call it Table1

The data table it is looking up against has
Year, Salesperson as rows, then the months across but only for the months it has data for as it's pivoted from Get and Transform.
Call it Table2

I want to return the values from Table2 based on sales person and year in B3 & A2 but also accomodate for when Table2 grows. I.e. if another month is added and include total row.

When I look up against one item it works as either:
=IFNA(VLOOKUP($B$3,Table2l[#All],MATCH(Table1[[#Headers],[January]],Table2[[#Headers],[Consultant]:[October]],0),0),"")

or
=IFNA(VLOOKUP($B$3,Table2[[#Data],[#Totals],[Consultant]:[Totals]],MATCH(Table1[[#Headers],[January]],Table1[[#Headers],[Consultant]:[Totals]],0),0),"")

but struggling on how I add in the addition of the year and accommodate for when extra months are added without having to change the formulas?

I can do a sumifs formula instead if not but wondered what the best way to do this is?

Many thanks for any advise.

 

[alansidman]

Attach a sample picture . Make sure there is just enough data to demonstrate your need. Include a BEFORE and AFTER scenario to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

 

[Melimob]

Hi thanks for the reply..

this works if I'm just looking up against the name..
=IFNA(VLOOKUP($B$3,tblMortgagesWritten[[Consultant]:[Totals]],MATCH(tblBrokerFig[[#Headers],[January]],tblMortgagesWritten[[#Headers],[Consultant]:[Totals]],0),0),"")

but I need it to look up to vlkup based on first two columns (year and name) so something like: (but this doesnt work)
=IFNA(VLOOKUP($A$2 & $B$3,tblMortgagesWritten,MATCH(tblBrokerFig[[#Headers],[January]],tblMortgagesWritten[#Headers],0),0),"")

tblBrokerFig - should return values from bottom table based on 'John Smith' in B3 and Year in A2 being selected..

Type	January	February	March
Written Business (Mortgages)

TableMortgagesWritten

Written Year	Consultant	January	February	March
2019	John Smith	9​	6​	6​

many thanks for any advice..

 

[DanteAmor]

Try this

Book1
	A	B	C	D	E
1
2	2019
3		John Smith
4
5
6	Type	January	February	March
7	Written Business (Mortgages)	9	80	17
8
9
10	Written Year	Consultant	January	February	March
11	2019	John Smith	9	80	17
12	2019	gus	10	81	18
13	2019	will	11	82	19
14	Total
Hoja5

Cell Formulas
Range	Formula
B7	B7	=SUMPRODUCT((TableMortgagesWritten[Consultant]=$B$3)*(TableMortgagesWritten[[#Headers],[January]:[March]]=tblBrokerFig[[#Headers],[January]])*TableMortgagesWritten[[January]:[March]])
C7	C7	=SUMPRODUCT((TableMortgagesWritten[Consultant]=$B$3)*(TableMortgagesWritten[[#Headers],[January]:[March]]=tblBrokerFig[[#Headers],[February]])*TableMortgagesWritten[[January]:[March]])
D7	D7	=SUMPRODUCT((TableMortgagesWritten[Consultant]=$B$3)*(TableMortgagesWritten[[#Headers],[January]:[March]]=tblBrokerFig[[#Headers],[March]])*TableMortgagesWritten[[January]:[March]])

 

[Melimob]

thank you Dante...the below works fine but I can't see that the A2 cell (Year) is referenced?

=SUMPRODUCT((tblMortgagesWritten[[Consultant]:[Consultant]]=$B$3)*(tblMortgagesWritten[[#Headers],[January]:[Totals]]=tblBrokerFig[[#Headers],[February]])*tblMortgagesWritten[[January]:[Totals]])

i.e. if there was another row with John Smith but the year was 2020, I need it to match up on the year also?

I tried variations to add this myself - see below, but it didn't work I'm afraid

thank you in advance!

=SUMPRODUCT((tblMortgagesWritten[[Consultant]:[Consultant]]=$B$3)*(tblMortgagesWritten[[Written Year]:[Written Year]]=$A$2)*(tblMortgagesWritten[[#Headers],[January]:[Totals]]=tblBrokerFig[[#Headers],[January]])*tblMortgagesWritten[[January]:[Totals]])

 

[DanteAmor]

Sorry, I put a previous test. Here the complete formula.

=SUMPRODUCT((TableMortgagesWritten[Consultant]=$B$3)*(TableMortgagesWritten[Written Year]=$A$2)*(TableMortgagesWritten[[#Headers],[January]:[March]]=tblBrokerFig[[#Headers],[January]])*TableMortgagesWritten[[January]:[March]])

 

[Melimob]

Sorry, I put a previous test. Here the complete formula.

=SUMPRODUCT((TableMortgagesWritten[Consultant]=$B$3)*(TableMortgagesWritten[Written Year]=$A$2)*(TableMortgagesWritten[[#Headers],[January]:[March]]=tblBrokerFig[[#Headers],[January]])*TableMortgagesWritten[[January]:[March]])

Hi Dante - thank you again for your time - at first it wouldn't work however I realised my year values were set as text so changing the format fixed it!

thank you again!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.