CODEWITHSUNDEEP
excelif-statementexcel-2010vlookup
Anthony Olivieri
Anthony Olivieri

Reputation: 15

VLOOKUP - IF Statement - Column Compare - YES or NO result desired

Last question was answered so quickly & accurately, I figured I'd give it another go!

So in Column A, there's a list of Magazine names. In Column C there's a list of Advertiser's names. Magazine titles & Advertiser names may repeat like so...

Time Magazine (Column A) ........ Amazon (Column C)
Time Magazine (Column A) ........ Amazon (Column C)
Forbes (Column A) ........ Google (Column C)
Forbes (Column A) ........ Amazon (Column C)

On a separate sheet in cell A1 I have a Data Validation drop down with the names of Advertiser's from Column C mentioned before.

I'm looking to input a formula which will lookup the Advertiser's name selected by the data validation drop down & tell me YES or NO to whether their name is ever associated with a specific magazine title in Column A, such as Forbes.

So in the example above, I'd want to be able to select the Advertiser "Google" from my data validation drop down & see a YES, indicating to me they do advertise in Forbes.

Hopefully this makes sense!

Upvotes: 0

Views: 247

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

Try this:

=IF(MAX(INDEX((Sheet1!A2:A99=A2)*(Sheet1!C2:C99=$A$1),,)),"Yes","No")

Assumptions:

  • This formula will be used in a cell on the 2nd sheet.

  • Sheet with raw data is named Sheet1. Change the formula appropriately.

  • Raw data extends no further than row 99; change the 99s in the formula as appropriate.

  • Raw data start in row 2.

  • On the sheet with the drop-down, the drop-down is in cell A1. Change as appropriate.

  • On the sheet with the drop-down, the magazine you wish to check the advertiser against is in cell A2. Change as appropriate.

Upvotes: 1

Related Questions