Can I VLOOKUP - IF?

[UniAmyColeman]

Hi All

I hope this makes sense to some of you! Because after my fruitless attempts it makes less sense to me now that when I started out!

I have this data (this is a fictional representation of the names!) in an excel worksheet tab called "Confirmation Forms" -

	A	B	C	D	E	F	G
1	Submitted	Owner first name	Owner last name	Status	Buyer first name	Buyer last name	Org name
2	12/07/2018	John	Badger	Buyer			Org A
3	12/07/2018	Sue	Giraffe	Owner	Paul	Rhino	Org B
4	12/07/2018	Aisha	Lion	Buyer			Org D
5	12/07/2018	Paul	Rhino	Buyer			Org B
6	12/07/2018	Leila	Horse	Owner	John	Badger	Org A
7	12/07/2018	Jaz	Hamster	Owner	Jessi	Cat	Org C
8	12/07/2018	Jessi	Cat	Buyer			Org C
9	12/07/2018	Janet	Fox	Owner	Aisha	Lion	Org D

<tbody>
</tbody>

Each owner and buyer is asked to submit a form, this is the collated data.

In a separate worksheet tab called "External Finance" Column R is titled "Org form submitted". In this worksheet "Org name" is in column A.

This is what I would like to do- In column R of "External Finance" - some sort of indicator that the "Owner" (Column D of "Confirmation Forms" tab) has submitted the form. I don't mind if this appears as the Org name when it has been submitted and left as N/A if it hasn't, or if there is a fancy way of showing it as a yes/no.

I would then replicate this in column S of External Finance but for the Buyer. I need to see that both have submitted the form. (The Org only has one row!)

I am struggling to put together a VLOOKUP with an IF function (I assume VLOOKUP by "Org Name" with an IF for "Status") - but recognise I may be on the wrong path completely.

Any advice greatly appreciated.
Amy

 

[wideboydixon]

Hi Amy,

Can you perhaps just use COUNTIFS? In R2 of External Finance:

=IF(COUNTIFS('Confirmation Forms'!$G:$G,$A2,'Confirmation Forms'!$D:$D,"Owner")>0,"Yes","No")

In S2:

=IF(COUNTIFS('Confirmation Forms'!$G:$G,$A2,'Confirmation Forms'!$D:$D,"Buyer")>0,"Yes","No")

WBD

 

[UniAmyColeman]

Hi Amy,

Can you perhaps just use COUNTIFS? In R2 of External Finance:

Wow - I hadn't even considered that! Works perfectly! Thank you

 

[UniAmyColeman]

Ok, can I complicate it a bit more?

Some owners have more than one buyer, can I add another argument checking IF the surname in Column F of "Confirmations forms" is the same as that in column F of the "External Finance Tab"?

 

[wideboydixon]

Just an extra condition I guess:

=IF(COUNTIFS('Confirmation Forms'!$G:$G,$A2,'Confirmation Forms'!$F:$F,$F2,'Confirmation Forms'!$D:$D,"Buyer")>0,"Yes","No")

WBD