INDEX MATCH Excel Formula Assistance

[rmdino5]

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'} span.s1 {color: #006107} span.s2 {color: #0057d6} span.s3 {color: #ab30d6} </style>I am working on a project and have been spending hours looking into the excel INDEX MATCH formulas and online tutorials using two different sheets. I have listed a detailed description of the data and what I am trying to accomplish. I am currently using Excel 2011. Any advice or help with my formula would be greatly appreciated!!


Below is a description of the Wine Distribution data On Sheet 2:
Column B (Producer), Column C (Vintage/Year), Column H (Bought From).

Below is a description of the Wine Distribution data On Sheet 1:

Column C (Producer), Column D (Vintage/Year), Column J (Bought From)

Description of Goal:
All data in columns described above have values in most of the cells under the data headers (headers in row 1). The only column without data present below the heading is Column H (Bought From) in Sheet 2. Both sheets have thousands of rows of records and contain a lot of the same data (example: A wine bought will be listed on sheet 2 with the Producer [ex. Ausone] and the Vintage/Year [ex. 2001], but not the Bought From Source. The same wine data is listed on sheet 1 (Ex: Producer = Ausone and Vintage/Year = 2001) but will also include the "Bought From" value for the wine. I was trying to come up with an INDEX MATCH formula in cell H2 of the "Bought From" column in sheet 2 to look at the Producer (column B) and Vintage/Year (column C) of sheet 2 row, which would look for matching values in both the Producer (column C) and Vintage/Year (column D) columns of sheet 1 and return the corresponding "Bought From" value in the same row of column J. I would like to run the formula to find all missing "Bought From" values in column H of sheet 2.


So far the formula I came up with was:

=INDEX('Portfolio by Producer (2)'!J:J,MATCH(D2&C2&E2, 'Portfolio by Producer (2)'!D:D&'Portfolio by Producer (2)'!C:C&'Portfolio by Producer (2)'!E:E, 0))

I received #VALUE! instead of content in cell H2.

The previous formula I ran was:

=INDEX('Portfolio by Producer (2)'!J2:J3643,MATCH(C69&D69,'Portfolio by Producer (2)'!C2:C3643&'Portfolio by Producer (2)'!D2:D3643,0))

This attempt yielded the #n/a error.

 

[AhoyNC]

Your formula is an array formula and need to be entered with command-return on a MAC or CTRL-SHIFT-ENTER on a PC.
=INDEX('Portfolio by Producer (2)'!J:J,MATCH(D2&C2&E2, 'Portfolio by Producer (2)'!D:D&'Portfolio by Producer (2)'!C:C&'Portfolio by Producer (2)'!E:E, 0))

If done right Excel will but {} around the formula.

You could also do something like below. This is also an array formula.

Excel Workbook
	B	C	D	H
1	Prod.	V/Year		Bought
2	A	2002		Bou1
3	B	2005		Bou2
4	A	2007		Bou3
5	B	2006		Bou4
Sheet2

Excel Workbook
	C	D	E	I	J
1	Prod.	V/Year			Bought
2	A	2002			Bou1
3	B	2005			Bou2
4	A	2007			Bou3
5	B	2006			Bou4
Sheet1

 

[Aladin Akyurek]

In h2 of Sheet2 control+shift+enter, not just enter, and copy down:

=index(Sheet1!$j$2:$j$400,match($b2,if(Sheet1!$d$2:$d$400=$c2,Sheet1!$c$2:$c$400),0))