CODEWITHSUNDEEP
excelfunctionexcel-formulaworksheet
thedream7
thedream7

Reputation: 1

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

Upvotes: 0

Views: 91

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

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

Upvotes: 1

L42
L42

Reputation: 19727

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.

Upvotes: 2

Related Questions