Thursday, 22 September 2016

How to Get the Cell Address of a Matching Column and Row in Excel

Excel Function to Get the Address of Matching Column and Row in Excel
Suppose we have the Field or Column names as follows in the source data tab as follows :


Now in our calculation sheet, if you want to capture the Address/Column Index Number/Name details of the lookup Fields/Column Names, we can do as follows :

Lets say the source data tab is "Data", and our lookup Fileds/Columns names in Column A which exists in Row 1 in source data tab, then now the formulas to get each item from source is as follows:
Field Address in Source:
 =(ADDRESS(1,MATCH(A2,Data!$A$1:$AH$1,0),1))

Field Row Number:
 =MID(B2,FIND("$",B2,2)+1,LEN(B2)-FIND("$",B2,2))

Field Column Number:
 =MATCH(A2,Data!$A$1:$AH$1,0)

Field Column Index Name:
 =LEFT(B2,FIND("$",B2,2)-1)

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts