Vlookup and Match with two criteria

[MrBurn5]

I'm working on a little project to track real estate transactions and I'm running in to issues. Data needs to be looked up by zone and suburb code in the first two columns. I then want to pull the listings from each year and average sale price from each year (in different formulas obviously) to chart the growth and shrinkage.

I followed a tutorial I found but am not getting the same outcome. I don't know if this is because I'm using all alpha numeric inputs or not but I get the #N/A error none-the-less. Any help would be appreciated.

Excel 2016 (Mac) 32 bit A B C D E F G H I J K L M N O P Q R S T U V 1 2 3 2016 2015 2014 2013 4 Area Area Suburb (SIC) Listings Avg. Price Med. Price Listings Avg. Price Med. Price Listings Avg. Price Med. Price Listings Avg. Price Med. Price Zone Suburb 5 e12 ew 6 East West Chester E12 EW 294 $230,677 $210,000 272 $207,200 $194,950 263 $202,834 $187,000 258 $182,244 $176,750 #N/A #N/A #N/A #N/A 7 Amelia C01 AM 92 $149,100 $146,875 90 $139,428 $140,000 79 $123,676 $130,000 79 $121,913 $128,000 8 Batavia C01 BA 26 $96,138 $107,500 22 $122,543 $114,500 16 $93,112 $97,450 15 $67,114 $38,107 9 Batavia Twp. C01 BT 336 $184,628 $175,000 307 $177,301 $175,000 259 $175,338 $168,000 237 $160,511 $150,500 10 Pierce Twp. C01 PI 154 $232,909 $220,000 128 $228,779 $213,000 141 $214,679 $182,000 117 $212,812 $179,250 11 Union Twp. C01 UN 667 $187,801 $167,000 655 $173,957 $153,900 558 $168,050 $155,000 592 $157,859 $149,175 12 Loveland C02 LO 24 $317,168 $213,500 41 $299,886 $242,000 34 $325,218 $242,500 42 $323,186 $346,000 13 Miami Twp. C02 MI 635 $263,609 $243,000 599 $243,883 $220,000 586 $236,679 $207,450 577 $238,304 $210,000 14 Milford C02 ML 61 $187,386 $154,900 61 $171,452 $147,000 51 $144,913 $125,000 51 $160,827 $130,000 15 Bethel C03 BE 36 $87,819 $94,113 38 $70,416 $60,750 25 $81,991 $74,000 30 $61,498 $47,000 <tbody> </tbody> page 1​ Array Formulas Cell Formula S6 {=INDEX(B6:F173,MATCH(1,(B6:F173=S5)*(C6:F173=T5),0),4)} T6 {=INDEX(C6:I173,MATCH(1,(C6:I173=T5)*(D6:I173=U5),0),7)} U6 {=INDEX(D6:L173,MATCH(1,(D6:L173=U5)*(E6:L173=V5),0),10)} V6 {=INDEX(E6:O173,MATCH(1,(E6:O173=V5)*(F6:O173=W5),0),13)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself <tbody> </tbody>

<tbody>
</tbody>

 

[cerfani]

what results are you trying to get in S6:V6?

since you are trying to look up multiple values for two fields (Listing & Avg Price)

Can you explain exactly where all 8 values are going? And where are your lookup values? You are only trying to find it for the inputs highlighted in yellow? You explained a lot but the whole picture is still a little vague.

 

[Marcelo Branco]

Try this array formula in S6 - observe carefully the references in D$6:D$173 (relative column); others ranges should use absolute columns and rows references
=INDEX(D$6:D$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))
Ctrl+Shift+Enter

Copy across till AD6

Hope this helps

M.

 

[MrBurn5]

Yes! That's it. Matrixes are not my forté and I'm still getting the hang of them. That seems to have worked when I applied it to the subsequent columns. Thank you Marcelo.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[MrBurn5]

Hold up, something unexpected popped up... I now get the results I want in S7:V8 where it shows the total listings and average prices. I can chart the listings fine in S8:V8, but when I chart the prices in S7:v7 the results on the chart are all zeros with a range of 0-1 on the x axis.

Excel 2016 (Mac) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
3				2016			2015			2014			2013
4	Area	Area	Suburb (SIC)	Listings	Avg. Price	Med. Price	Listings	Avg. Price	Med. Price	Listings	Avg. Price	Med. Price	Listings	Avg. Price	Med. Price				Zone	Suburb
5																			C03	WT
6	East West Chester	E12	EW	294	$230,677	$210,000	272	$207,200	$194,950	263	$202,834	$187,000	258	$182,244	$176,750				2013	2014	2015	2016
7	Amelia	C01	AM	92	$149,100	$146,875	90	$139,428	$140,000	79	$123,676	$130,000	79	$121,913	$128,000			Price	$87,744	$134,183	$161,636	$143,317
8	Batavia	C01	BA	26	$96,138	$107,500	22	$122,543	$114,500	16	$93,112	$97,450	15	$67,114	$38,107			Listings	27	29	31	38
9	Batavia Twp.	C01	BT	336	$184,628	$175,000	307	$177,301	$175,000	259	$175,338	$168,000	237	$160,511	$150,500
10	Pierce Twp.	C01	PI	154	$232,909	$220,000	128	$228,779	$213,000	141	$214,679	$182,000	117	$212,812	$179,250
11	Union Twp.	C01	UN	667	$187,801	$167,000	655	$173,957	$153,900	558	$168,050	$155,000	592	$157,859	$149,175
12	Loveland	C02	LO	24	$317,168	$213,500	41	$299,886	$242,000	34	$325,218	$242,500	42	$323,186	$346,000
13	Miami Twp.	C02	MI	635	$263,609	$243,000	599	$243,883	$220,000	586	$236,679	$207,450	577	$238,304	$210,000
page 1

Cell Formulas
Range	Formula
S7	{=INDEX(N$6:N$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
S8	{=INDEX(M$6:M$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
T7	{=INDEX(K$6:K$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
T8	{=INDEX(J$6:J$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
U7	{=INDEX(H$6:H$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
U8	{=INDEX(G$6:G$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
V7	{=INDEX(E$6:E$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
V8	{=INDEX(D$6:D$173,MATCH(1,($B$6:$B$173=$S$5)*($C$6:$C$173=$T$5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

I don't understand what you are trying to do. You wrote in post #1
"I then want to pull the listings from each year and average sale price from each year..."

Which results(values) do you want and where they should be displayed? Please, clarify.

M.

 

[MrBurn5]

I figured out the problem - don't laugh.

The your formulas did exactly what they were supposed to do, but I did not know that the data was converted from a PDF in the most literal sense. So all of the "$" signs were causing the chart to read is as though it were a letter.

 

[Marcelo Branco]

Happy you figured it out

M.

 

[MrBurn5]

(sheepishly crawls back under desk)