excelexcel-formulaspreadsheet

display 'Received" if a row contains referenced loan number and doc type

I have an Excel sheet that lists all the documents that have been received.

Loan number Last Name Doc Type
354 Smith Title
354 Smith Deed
354 Smith Mortgage
765 Wilson Mortgage
765 Wilson Deed

I'm trying to write a formula that will fill in an existing sheet, I have shown below, with "Received" if the document is listed in the Received table, above, for the specific Loan Number.

Loan Number Last Name Title Mortgage
354 Smith Received Received
765 Wilson Received

I'm looking for a formula that I can paste in the Title column that will see if the Title has been received for the loan number referenced in the row. And the same for the Mortgage column.

I've messed around with using filter and vlookup, match, and if, but don't know how to put it all together. I hope I explained this well enough ... Let me know if you have any questions.

Solution

  • You might also try working with the FILTER() worksheet function. Hereby an example, based on the following formula:

    =COUNT(FILTER($B$3:$D$7;($B$3:$B$7=B3)*($C$3:$C$7=C3)*($D$3:$D$7="Title");""))

    Result (equal background means that they refer to the same formula: one cell contains the FormulaText() of the other):

    Excel screenshot