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" -

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Submitted[/TD]
[TD]Owner first name[/TD]
[TD]Owner last name[/TD]
[TD]Status[/TD]
[TD]Buyer first name[/TD]
[TD]Buyer last name[/TD]
[TD]Org name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/07/2018[/TD]
[TD]John[/TD]
[TD]Badger[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/07/2018[/TD]
[TD]Sue[/TD]
[TD]Giraffe[/TD]
[TD]Owner[/TD]
[TD]Paul [/TD]
[TD]Rhino[/TD]
[TD]Org B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12/07/2018[/TD]
[TD]Aisha[/TD]
[TD]Lion[/TD]
[TD]Buyer [/TD]
[TD][/TD]
[TD][/TD]
[TD]Org D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/07/2018[/TD]
[TD]Paul[/TD]
[TD]Rhino[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org B[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12/07/2018[/TD]
[TD]Leila[/TD]
[TD]Horse[/TD]
[TD]Owner[/TD]
[TD]John [/TD]
[TD]Badger[/TD]
[TD]Org A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]12/07/2018[/TD]
[TD]Jaz[/TD]
[TD]Hamster[/TD]
[TD]Owner[/TD]
[TD]Jessi[/TD]
[TD]Cat[/TD]
[TD]Org C[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]12/07/2018[/TD]
[TD]Jessi[/TD]
[TD]Cat[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org C[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]12/07/2018[/TD]
[TD]Janet[/TD]
[TD]Fox[/TD]
[TD]Owner[/TD]
[TD]Aisha[/TD]
[TD]Lion[/TD]
[TD]Org D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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