Need to consolidate based on criteria without macro or vlookup

[JSH720]

Can't get the formula right. I have two tabs in a spreadsheet with many rows and columns. I need to pull the need contrac t a nd comments from Tab 2 to the corresponding address in tab 1 (which has a bunch of other info in it) Trying to use index match (don't want to use vlookup- long story). but I keep getting errors. The address is a unique value and there are no duplicates. The number of addresses on easch sheet is different, not all on 1 are on 2 and vice vers. See below

Tab 1 (summary tab)
Address Need Contract comments
1234
5678
9112

Tab 2 (detail tab)
Address Need Contract comments
5568 no not in neighborhood
1234 yes not fully executed
9112 yes subject to partnership
2222 no

New Summary Tab
Address NEED CONTRACT COMMENTS
1234 YES NOT FULLY EXECUTED
5568 NO NOT IN NEIGHBORHOOD
9112 YES SUBJECT TO PARTNERSHIP
2222 NO
5678

[TABLE="width: 508"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Can't use VBA, due to policy.


[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Here's the basic formula:

=INDEX('Tab 2'!B$2:B$10,MATCH(A2,'Tab 2'!$A$2:$A$10,0))

change the references as needed, drag down and to the right as needed.

 

[JSH720]

Here's the basic formula:

=INDEX('Tab 2'!B$2:B$10,MATCH(A2,'Tab 2'!$A$2:$A$10,0))

change the references as needed, drag down and to the right as needed.

Thank you eric, this worked like a charm once I was able to get back to the project!!!

julie

 

[Eric W]

Glad it works for you!