I have three sets of data, Set1, Set2, and set3, which are of different lengths, e.g. a set may look like:
Address Comment
A200.11 First Cycle Flag
A200.12 Step Flag
A200.15 First Task Execution Flag
In this compare, I take the address, and use a VLOOKUP, to check wether the address exists in the other data set, like so:
=IF(ISNA(VLOOKUP(B3,$D$3:$D$288,1,FALSE)),"Address OK", "Address Exists")
Where B3 is the cell containing the address, and $D$3:$D$288 is the address column for the other data set, i.e. my lookup table.
So, if the value of the VLOOKUP is null/doesn't exist, the address is OK, else it is NG.
This works well enough, but what I'd like to do, is IF the address exists in the other dataset, display, "ADDRESS NG" AND the COMMENT of that address in the data set.
Example: Say Dataset1, Cell, B62 contains address A200, and comment in cell C62 is "COMMENT" In Set2, Cell, D185 also contains address A200, and Cell E185 contans "COMMENT SET 2"; I would like the output of the function to be "Address Exists COMMENT SET 2"
I beleive this would require the use of the OFFSET function, but I'm unsure how to implement.