Index and Match with multiple criteria

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
160
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Attachments

  • 5C4E17AB-2BA0-42E2-9FF7-8BCB0F930A30.png
    5C4E17AB-2BA0-42E2-9FF7-8BCB0F930A30.png
    38.5 KB · Views: 19
Last edited:
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!
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top