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>

 

[Marcelo Branco]

(sheepishly crawls back under desk)

Now, i have to laugh...

Just kidding. Don't worry, such things happen to everyone

M.