Index and Match with multiple criteria

[Indominus]

Hello. The attached photo is just an example. So I have this workbook that has multiple sheets like this where I would have to run the same type of code. In one sheet I have at least two lookup values. Another sheet I have a file like the one attached. Referenced as Raw data. One lookup value which will be a person’s username will be in column A. I then have at least two numbers that I need to identify if the username has them attached. These are in column I in Raw data sheet. In the real sheet the lookup values, the username and two numbers, won’t be in column R but in my main sheet. If that matters. But what I need to be able to do is for example, is find
IF Bob has both 6383 and 9473, “pass”,”fail”. Basically an IF formula with this.

I have tried Index and match but it won’t work straight up since it will only find the first time Bob appears. I YouTubed this and it seems maybe I will have to use arrays or the small formula. Not 100% sure though. Any help would be very appreciated. Thank you in advance!

 

[Indominus]

Try

=IF((COUNTIFS(A:A,"bob",I:I,6383)>0)+(COUNTIFS(A:A,"bob",I:I,9473)>0)=2,"pass","fail")

M.

Thank you this works perfectly!!! Exactly what I needed. I really appreciate it. Didn’t realize I could use CountIfs. Also thank you for the assistance Dante!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[DanteAmor]

Glad we could help & thanks for the feedback