excelexcel-formula

Excel: how to search by two rows and one column

I have the attached file. In the first row, I can see the position of the player. In the second, the number. Based on this, I would like to know if the player played in the country listed in the downside. For example, I would like to query: Position= PG; Number= 2; Country = China and to get back Yes as response.

enter image description here

Solution

  • There are dozens of ways of doing it. Perhaps the simplest one will be using INDEX and MATCH which should work on any Excel version:

    =INDEX(A1:E5,
      MATCH("Spain",A1:A5,0),
      MATCH(1, (A1:E1="SG")*(A2:E2=1), 0))

    enter image description here