Forum Discussion
Two Criteria Search
I'm trying to knock some cobwebs off my excel.
I'm attempting to filter an array with 2 input criteria to then search for a designator.
Say I have Locations
A,B,C,D
And Jobs
1,2,3,4
With People
Bob, Sue, Joe, Fin
Arrayed as such
| A | B | C | D |
| 1 | 2 | 3 | 4 |
| Bob | Sue | Fin | Bob |
| Joe |
With say a reference to Job = "1"
Person = "Bob"
When putting in said criteria into reference cells,
I'm looking to acquire an output of "A"
So I know if "Bob" is on Job "1", he is at location A.
How could I accomplish this?
See screenshot. With the person's name in B6 and the job in B7, the location is returned by
=IFERROR(INDEX(A1:D1,MATCH(1,((A3:D3=B6)+(A4:D4=B6))*(A2:D2=B7),0)),"-")
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
3 Replies
See screenshot. With the person's name in B6 and the job in B7, the location is returned by
=IFERROR(INDEX(A1:D1,MATCH(1,((A3:D3=B6)+(A4:D4=B6))*(A2:D2=B7),0)),"-")
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
- Okay I see how this works, thank you.
Small question though,
When adding people into the table, would I have to add another line to the Match.
With it currently (A3:D3=B6)+(A4:D4=B6) would I then have to add (A5:D5=B6) to make the formula account for the new row? or is there a way to have it account for an entire array of A3:D5?I'd change the layout on
and use something like
=XLOOKUP( B7&"="&B8, peopleByLocationAndJob[name] & "=" & peopleByLocationAndJob[Job], peopleByLocationAndJob[Location], "no such" )
