Reference based on if data appears on anther worksheet

**MarcLewis** · 02-22-2014, 04:28 AM

Morning All,

I currently have 2 worksheets, worksheet 1 is called "PML" & worksheet 2 is called "Completed"

PML has 3 columns, 1- survey, 2-install, 3-site code
Completed has 2 columns, 1-site code, 2- visit type (2 options, 1-survey, 2-install)

I would like for PML-Survey to display the text "completed " if PML-site code appears in Completed-site code with Completed-visit type set as survey.

I have attached a demo workbook in case my explanation isn't up to scratch.

Many Thanks in advance

status.xlsx

**BadlySpelledBuoy** · 02-22-2014, 04:39 AM

Try this in cell A2 of the PML worksheet and copy down.

=IF(COUNTIFS(Completed!A:A,C2,Completed!B:B,A$1)>0,"Completed","")

**AZ-XL** · 02-22-2014, 04:54 AM

Hi
BadlySpelledBuoy's solution is easiest and simplest. Mine is just an alternative

PML sheet a2 cell =IFERROR(INDEX(Completed!$B$2:$B$15,MATCH(1,(PML!$C2=Completed!$A$2:$A$15)*(PML!A$1=Completed!$B$2:$B$15),0)),"")
Then hit Ctrl+Shift+Enter