Here I am going to show you Lookup with multiple criteria's through formula.
For example in the figure below, we require the Age of "John" who is based out in city "New York". Now there are two John mentioned in the table array. So we will create a formula that will take two criteria's i.e. both Name and city to identify the age.
1st Alternative:
Using XLookup
Enter formula:
=XLOOKUP(E4&F4,A2:A9&B2:B9,C2:C9)
2nd Alternative:
Using FILTER
Enter formula:
=FILTER(C2:C9,(A2:A9=E4)*(B2:B9=F4))
3rd Alternative:
Using VLOOKUP and CHOOSE
Enter formula:
=VLOOKUP(E4&F4,CHOOSE({1,2},A2:A9&B2:B9,C2:C9),2,0)
Download working file👈👨💻
Thank You!!