Forum Discussion
Solved
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 P...
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?
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" )