Here we are going to demonstrate how we can get the lookup result on multiple rows and columns by entering formula in just a single cell.
Its one of the most Dynamic formula combination of excel
Just look at the the above two tables. We want to get the Age and State for all lookup values in Column "E". Now if you further observe you will see that the lookup column order has also changed in Table 2. Here we require "Age" first in column "F" and then the "State" in column "G".
We will use only a single formula to update all the values through Dynamic Array.
To do this we will insert the below formula in Cell "F3".
=INDEX(A1:C9,MATCH(F3:F10,A1:A9,0),MATCH(G2:H2,A1:C1,0))
Download working file👈👨💻
Thank You!