Cross reference two sheets and then check another column

[dagindi]

Attached is a spreadsheet with the sample data. I am trying to compare two lists in the following manner and return 1 of 3 possible answers.

I want to compare "Work Sheet" column A and "Master List" column A and then:

If there is no match then Column D on Master List should return a value of "FALSE"

If there is a match BUT column G on "Work Sheet" is blank then Column D on Master List should return a value of "BLANK"

If there is a match AND column G on "Work Sheet" is NOT blank then Column D on Master List should return a value of "TRUE"

I can check each one separately with MATCH or CONDITIONAL FORMATTING but for some reason I cant figure out how to combine this into one equation string.

Thanks in advance!

[rylo]

Hi

Try this function.

Work Sheet!D2: =IF(COUNTIF('Master List'!A:A,'Work Sheet'!A2)=0,FALSE,IF(ISBLANK(G2),"BLANK",TRUE))

Copy down as required.

HTH

rylo

[foxguy]

Hi dagindi;

Rylo's formula sets WorkSheet!D2. I think you want Master!D2 to contain your formula

Master!D2 : =If(IsError(Match(A2,'WorkSheet'!A:A,0)),False,If(Index('WorkSheet'!G:G,Match(A2,'WorkSheet'!A:A,0),1)="","Blank",True))

And copy down column D