The following formula lives in cell C1 on Sheet1:
=IF(COUNTIF(Sheet2!83:83,B1)>0,IF((MATCH(B1,Sheet2!83:83,0))<=5,B1,A1),A1)
It determines if B1 on Sheet1 occurs in the first five columns of row 83 on Sheet2 and returns A1 or B1 based on this logical argument.
On Sheet2, column A is an alphabetically sorted listed of names. Columns B thru J contain values associated with the names in column A.
In the above formula, the term "Sheet2!83:83" forces a reference to row 83 on Sheet2. How do I change this "forced" reference to use a name in Sheet1 cell A2 to find the corresponding row in Sheet2? In other words, I would like to input a name in cell A2 on Sheet1, find its match on Sheet2 column A and use that row instead of having "Sheet2!83:83" "hard coded" into the formula.
=IF(COUNTIF(Sheet2!83:83,B1)>0,IF((MATCH(B1,Sheet2!83:83,0))<=5,B1,A1),A1)
It determines if B1 on Sheet1 occurs in the first five columns of row 83 on Sheet2 and returns A1 or B1 based on this logical argument.
On Sheet2, column A is an alphabetically sorted listed of names. Columns B thru J contain values associated with the names in column A.
In the above formula, the term "Sheet2!83:83" forces a reference to row 83 on Sheet2. How do I change this "forced" reference to use a name in Sheet1 cell A2 to find the corresponding row in Sheet2? In other words, I would like to input a name in cell A2 on Sheet1, find its match on Sheet2 column A and use that row instead of having "Sheet2!83:83" "hard coded" into the formula.