TAGS :Viewed: 8 - Published at: a few seconds ago

[ Microsoft Excel 2010 Lookup between 2 worksheets ]

I have 2 worksheets in Excel, and I’d like to create a column in the first worksheet that returns a value based on whether a column of Worksheet 1 exists in a column in Worksheet 2. For example, the first worksheet, ‘FavoriteColors’:

Name    |  FavoriteColor

John    |   Blue

Betty   |  Green

Joe     |  Blue

Fred    |  Yellow

Beth    |  Blue

The second worksheet is called ‘BlueFans’ and is as follows:

Name

John

Betty

Beth

I want to add a column to the FavoriteColors worksheet, using a function that looks up to see if the Name exists in the second worksheet BlueFans, and returns a respective value, as follows:

Name    |   FavoriteColor    |  Is_it_blue

John    |   Blue             |  Yes

Betty   |   Green            |  No

Joe     |   Blue             |  Yes

Fred    |  Yellow            |  No

Beth    |   Blue             |  Yes

I have to leverage the second worksheet. I can’t simply use a function that uses the logic: “If favorite color = blue then ‘yes’ else ‘no’. Any assistance would be appreciated

Answer 1


try this:

=IF(ISERROR(MATCH(A2,BlueFans!A:A,0),"NO","YES")

Assuming your data in BlueFans is in Column A.
And your Data in FavoriteColors are in Column A and B starting in 2n row.
Put formula in C2.
Hope this is close to what you want.

Answer 2


I don't know what you mean by leverage the second worksheet. But a formula on Sheet1 that will return Yes or No, depending on whether the Name in Column A exists on BlueFans would be:

=IF(COUNTIF(BlueFans!$A$1:$A$100,A2),"Yes","No")