Index Match or Vlookup with Multiple Criteria

[mariett]

Hello,

I'm trying to query values from one tab into another using either an index match or vlookup formula with multiple criteria. I've created an example of my file here.

I want to avoid manually copying/pasting my values for each rep, month and metric by using one of these formulas, but so far I haven't had any success with writing the formula. Does anyone know wheat might be missing here?

Thanks

 

[Gimics]

Hey Mariett - you're on the right track, but your ranges are different sizes so the results of your MATCH formula are not going to align with the array of your INDEX formula.

INDEX works easiest when you're feeding it both a column value and a row value (=INDEX(array,row,column). If you split your formula up to look at your columns (B & C) for the right row number, and your row (2) for the right column number, then passed them into the INDEX formula, you'd have better results.

The simplest way to perform a multi-value lookup for MATCH'ing is to use a helper column with concatenation. Basically on your first sheet you create a column where you insert a formula that concatenates the results of column B with column C (something like =B3&C3, or =CONCAT(B3,C3). Then you have a single column that you can use to lookup your "row" value for your index formula. Say if you added this formula to column "A", you could use the formula:

=INDEX('Example Targets File'!$D:$O,MATCH($A2&$E2,'Example Targets File'!$A:$A,0),MATCH(D2,'Example Targets File'!$2:$2,0)

The last thing to note -it looks like you're in a country that uses a different date format that me, so there were issues with matching the dates in your second table with the dates in your first. Just make sure that they are the same values. You might not want to add 31 days to each in your first table as that's an easy way to throw some of the months off. Instead you could use the EDATE formula to just add 1 month ex. =EDATE(D2,1)

Hope that helps,

Book1.xlsx
	A	B	C	D	E	F
1	name	month_date	month formatted	month reversed	metric_name	metric_value
2	Rep A	1/1/2022	Jan-22	22-Jan	Metric 1	12
3	Rep A	2/1/2022	Feb-22	22-Feb	Metric 1	9
4	Rep A	3/1/2022	Mar-22	22-Mar	Metric 1	10
5	Rep A	4/1/2022	Apr-22	22-Apr	Metric 1	10
6	Rep A	5/1/2022	May-22	22-May	Metric 1	6
7	Rep A	6/1/2022	Jun-22	22-Jun	Metric 1	11
8	Rep A	7/1/2022	Jul-22	22-Jul	Metric 1	12
9	Rep A	8/1/2022	Aug-22	22-Aug	Metric 1	11
10	Rep A	9/1/2022	Sep-22	22-Sep	Metric 1	9
11	Rep A	10/1/2022	Oct-22	22-Oct	Metric 1	#REF!
12	Rep A	11/1/2022	Nov-22	22-Nov	Metric 1	#REF!
13	Rep A	12/1/2022	Dec-22	22-Dec	Metric 1	#REF!
14	Rep A	1/1/2022	Jan-22	22-Jan	Metric 2	16
15	Rep A	2/1/2022	Feb-22	22-Feb	Metric 2	11
16	Rep A	3/1/2022	Mar-22	22-Mar	Metric 2	14
Example Output File

Cell Formulas
Range	Formula
F2:F16	F2	=INDEX('Example Targets File'!$D:$O,MATCH($A2&$E2,'Example Targets File'!$A:$A,0),MATCH(D2,'Example Targets File'!$2:$2,0))

 

[Fluff]

How about

=INDEX('Example Targets File'!$E:$P,MATCH(F2,'Example Targets File'!$D:$D,0),MATCH(D2,'Example Targets File'!$E2:P2,0))

Although it will comeback as #N/A as the dates in one sheet are all 2023 & on the other sheet they are 2022

 

[Fluff]

Hey Mariett - you're on the right track, but your ranges are different sizes so the results of your MATCH formula are not going to align with the array of your INDEX formula.

So are yours

 

[Gimics]

So are yours

Nothing's "mine" - just went with OP's values to encourage some self-guided learning

Teach a person to fish, right?

 

[Fluff]

But the formula you posted has the same problem as you pointed out to the OP. So possibly miss-leading.

 

[mariett]

@Gimics @Fluff Great, thank you both! Didn't realize my example output file was using 2022 while my example targets tab had 2023 values In the real file, I'll be referring to two different 2022 and 2023 target tabs.

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.