Sometimes we require to lookup the information based on the information of two columns .
In below Example printshot we need to know the Total sales of Ajay Arora in North region .
As we can see the Employee name and the Region is in different columns ie. in Column B and Column C. So, we need to concatenate the contents of the both column range (i.e B1:B17&C1:C17) as well we need to concatenate the particular information (i.e H1&H2) which we need to find in the concatenated column for the purpose to lookup the TotalSales value which is in column E.
It is an Array formula as shown below:-
{=INDEX(E1:E17,MATCH(H1&H2,B1:B17&C1:C17,0))}
In below Example printshot we need to know the Total sales of Ajay Arora in North region .
As we can see the Employee name and the Region is in different columns ie. in Column B and Column C. So, we need to concatenate the contents of the both column range (i.e B1:B17&C1:C17) as well we need to concatenate the particular information (i.e H1&H2) which we need to find in the concatenated column for the purpose to lookup the TotalSales value which is in column E.
It is an Array formula as shown below:-
{=INDEX(E1:E17,MATCH(H1&H2,B1:B17&C1:C17,0))}
No comments:
Post a Comment