Vlookup, If statement and Indirect combined function help

I have a workbook with 4 spreadsheets. Sheet 1 maintains all numbers and compares 2 cells reflecting "Match ABC" and "DIFF". Sheet 2 contains all numbers from sheet 1 with a "DIFF" value. Again, it will compare 2 cells and reflect "MATCH DEF" and "DIFF". Sheet 3 contains all numbers from sheet 2 with a "DIFF" value; comparing 2 cells to reflect "MATCH GHI". Sheet 4, the final sheet, contains the remaining numbers and compares 2 cells again to reflect "MATCH JKL" and "DIFF".

Sheet 1 is the master sheet. I want to have it reflect the "match" result from all 4 sheets for the numbers...such as:

#
123 Match ABC
456 Match ABC
789 Match GHI
1234 Match JKL
12345 DIFF (this could happen)
123456 Match DEF

I've tried using the INDIRECT function combining with VLookup and IF to no avail.

Can this be done?

Originally Posted by martindwilson

attach a sample workbook showing exactly what you require

Here is a sample worksheet -- columns A-F is what I am working with for all 4 spreadsheets. Column G in sheet1 (PROP VAL) is what I am trying to achieve.

I am new to the forum so hopefully the file is attached correctly.

Thanks for your help.