Index Match

There are some cases where you need to fill one spreadsheet based on the field of another. This can be accomplished through Access with relationships but if you don't have Access or just want to quickly mash together some things in Excel index match can be useful.

The following is a simplified explanation of the formula:

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

So lets say for example that I have Table A that is missing the Parcel ID

Table A:

PARCELNUM	DOCUMENTID	PARCELID
12E1B -3600	doc1
12E1B -3700	doc2
12E1B -3800	doc3
12E1B -3800	doc4
12E1B -3900	doc5

I have Table B which has the PARCELID field, and I am using the DOCUMENTID to link the PARCELID field

Table B:

DOCUMENTID	PARCELID
doc2	84
doc1	82
doc3	85
doc5	81
doc4	83

I want the field from Table B, Column B (PARCELID) to be populated into Table A, Column C (PARCELID) Where the DOCUMENTID field from both tables match. I place the following formula into the empty column (C2) and drag down:

=INDEX(Sheet2!$B$2:$B$6,MATCH(B2,Sheet2!$A$2:$A$6,0))

You can also handle errors with something like this:

=IFERROR(INDEX(Sheet2!$B$2:$B$6,MATCH(B2,Sheet2!$A$2:$A$6,0))&"","")

If you did it correctly it should look something like this:

PARCELNUM	DOCUMENTID	PARCELID
12E1B -3600	doc1	82
12E1B -3700	doc2	84
12E1B -3800	doc3	85
12E1B -3800	doc4	83
12E1B -3900	doc5	81

As an aside you can also use vlookup which has a simpler syntax but also has more limitations