Lookup to find a value in two columns

0
votes

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.

2
excelexcel-formula
What formula did you try and where did you put it? – Jacob Edmond

2 Answers

2
votes

Use Countifs:

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

enter image description here

0
votes

How about this?

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

range would be your column, ex: B2:B99