How To Filter the Range in a VLOOKUP Function

[jahinton]

I need help to accomplish something with a VLOOKUP command. I have a feeling it involves using some kind of array but am just struggling trying to find a solution. Obviously I am NOT an Excel power user. So if you have an answer for me, please help me understand it with easy explanations.

I want to look up a value in a range based on a lookup value, but I need to filter other columns in the range I'm using before returning the value in the designated column. Let me show you an example of the data in the target range:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAYMENT[/TD]
[TD]PBM[/TD]
[TD]Account[/TD]
[TD]Product[/TD]
[TD]Measure[/TD]
[TD]2/9/2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Commercial[/TD]
[TD]*Total[/TD]
[TD]*Total[/TD]
[TD]Advair[/TD]
[TD]NBRx Shr[/TD]
[TD]32.67%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Commercial[/TD]
[TD]Aetna[/TD]
[TD]Coventry[/TD]
[TD]Advair[/TD]
[TD]NBRx Shr[/TD]
[TD]34.89%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Commercial[/TD]
[TD]Aetna[/TD]
[TD]Aetna[/TD]
[TD]Symbicort[/TD]
[TD]NBRx Shr[/TD]
[TD]40.28%[/TD]
[/TR]
</tbody>[/TABLE]

So this is what I'm trying to do. I'm trying to use the value in A2 below as my lookup value. Then reference the range above and return ONLY the value in cell F2 (32.67%). So I want to match the lookup value "Commercial" with the PAYMENT column but filter the rest of the columns so that I'm only selecting the "Commercial" value that has a value of "*Total" in column B, "*Total" in column C, "Advair" in column D, and "NBRx Shr" in column E. What kind of combination of functions do I need to do that?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAYMENT[/TD]
[TD]NBRx Share[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Commercial[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance to anyone who can help me!

 

[wideboydixon]

Book1
	A	B	C	D	E	F
1	PAYMENT	PBM	Account	Product	Measure	02/09/2017
2	Commercial	*Total	*Total	Advair	NBRx Shr	32.67%
3	Commercial	Aetna	Coventry	Advair	NBRx Shr	34.89%
4	Commercial	Aetna	Aetna	Symbicort	NBRx Shr	40.28%
Sheet1

Book1
	A	B
1	PAYMENT	NBRx Share
2	Commercial	32.67%
Sheet2

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet1!F:F,MIN(IF(Sheet1!A:A=$A2,IF(Sheet1!B:B="*Total",IF(Sheet1!C:C="*Total",IF(Sheet1!D:D="Advair",IF(Sheet1!E:E="NBRX Shr",ROW(Sheet1!A:A))))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[jahinton]

Oh thank you for the fast response!! I so appreciate it. However it is not QUITE working right for me. If I change the "Advair" value to "Symbicort" it is still showing the value of 32.67% from the Advair row. What am I to do differently?

 

[jahinton]

Also... why does it return 32.67% if I just hit enter, but if I treat it as an array formula and use Ctrl-Shift+Enter, I get a result of 42775. I don't even know where that number is coming from.

 

[wideboydixon]

It has to be an array formula in order to work. 42775 is 9th February in numeric form; you'll probably get this if there's no match on the criteria. Here's my working example again:

Book1
	A	B	C	D	E	F
1	PAYMENT	PBM	Account	Product	Measure	09/02/2018
2	Commercial	*Total	*Total	Advair	NBRx Shr	32.67%
3	Commercial	Aetna	Coventry	Advair	NBRx Shr	34.89%
4	Commercial	Aetna	Aetna	Symbicort	NBRx Shr	40.28%
5	Commercial	*Total	*Total	Symbicort	NBRx Shr	37.29%
Sheet1

Book1
	A	B
1	PAYMENT	NBRx Share
2	Commercial	32.67%
3	Commercial	37.29%
Sheet2

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet1!F:F,MIN(IF(Sheet1!A:A=$A2,IF(Sheet1!B:B="*Total",IF(Sheet1!C:C="*Total",IF(Sheet1!D:D="Advair",IF(Sheet1!E:E="NBRX Shr",ROW(Sheet1!A:A))))))))}
B3	{=INDEX(Sheet1!F:F,MIN(IF(Sheet1!A:A=$A3,IF(Sheet1!B:B="*Total",IF(Sheet1!C:C="*Total",IF(Sheet1!D:D="Symbicort",IF(Sheet1!E:E="NBRX Shr",ROW(Sheet1!A:A))))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[jahinton]

I'm not getting the same results. As I paste the formula in the next cell down (B3 in Sheet 2), I get the number from the next cell down from Sheet 1 (F3 in Sheet 1). That is if I enter the formula without entering it an array formula. If I enter it as an array formula, I get that number - 42775. Could this have anything to do with the fact that I'm on a Mac? I looked up the keys for entering an array on Mac and it was Ctrl-Shift-Enter. So I'm doing that and it puts the curly brackets in but returns 42775. Maybe I need to enter this question on a new thread???

 

[wideboydixon]

Note that it's checking for the pattern:

Commercial, *Total, *Total, Symbicort, NBRx Shr

If it doesn't find it, you'll get the date from the top of the column.

WBD