How to search multiple names for the right ID excel-CodePudding

I need to pull the ID of individuals based on their first and last names. I have about 800 names I will need to search from. This needs to be pulled weekly and not everyone shows up each week.

I split the names into first and last names using the Text to Columns function in excel data so its not the neatest.

My data looks something like this

The function is am using is this =INDEX(A:A,MATCH(E2,B:B,0)). How can I get excel to search the rest of the jones and pull back the right IDs?

Thanks

CodePudding user response：

There are different ways to do this. One option is the filter function.

=FILTER(A:A,(B:B=E2)*(C:C=F2))

CodePudding user response：

Or, if you're using an older version of Excel without the FILTER() function

=INDEX($A$2:$A$7,MATCH(E2&F2,$B$2:$B$7&$C$2:$C$7,0))

which should be entered as an array formula.