CODEWITHSUNDEEP
excelexcel-formula
user7242409
user7242409

Reputation:

Lookup to find a value in two columns

I have two sheets. Sheet1 has this data

Name    red blue
John    x   
John    x   
John        x
John    x   
John    x

Sheet2 has this data

Name    red blue
John

I needed a formula to populate an "x" under columns red and blue since data in Sheet1 has at least one "x" in the red and blue columns. I tried using vlookup/hlookup but it returns the first value. Same with indexmatch.

Upvotes: 0

Views: 47

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

Use Countifs:

=IF(COUNTIFS(Sheet1!$A:$A,$E2,Sheet1!B:B,"x"),"x","")

enter image description here

Upvotes: 2

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12670

How about this?

IF(COUNTIF(range,"x"),"x","")

range would be your column, ex: B2:B99

Upvotes: 0

Related Questions