IF / COUNTIF / MATCH row reference

[ScubaTig]

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.

 

[exceldevs]

Hi ScubaTig,

Try

=IF(COUNTIF(INDIRECT($A$2),B1)>0,IF((MATCH(B1,INDIRECT($A$2),0))<=5,B1,A1),A1)

 

[ScubaTig]

I can't get that to work. Doesn't look like anything in that formula tells it to look anywhere on Sheet2???

 

[Aladin Akyurek]

What do you mean by:

"On Sheet2, column A is an alphabetically sorted listed of names. Columns B thru J contain values associated with the names in column A"?

 

[markmzz]

If I understand correctly what you want, maybe this formula can help you:

In C1

=IF(ISERROR(MATCH(B1,INDEX(Sheet2!$B:$E,MATCH(A2,Sheet2!$A:$A,0),0),0)),A1,B1)

Markmzz

 

[ScubaTig]

That looks like it will work. THANK YOU!!!

 

[exceldevs]

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.

Hi ScubaTig,

From this statement I thought you would like to have a name defined in Sheet1!A2 (e.g. Sheet1!A2=table_rng where table_rng is the table name that you defined for let's say range Sheet2!A1:A83) Thus whenever range of table_rng changes, table_rng will follow and reflected in post#2 formula.

 

[markmzz]

That looks like it will work. THANK YOU!!!

You are welcome and I'm happy to help.

Markmzz

 

[ScubaTig]

exceldevs,
By "name" I actually meant something like a person's name in the data, not a range or reference name. Thanks for your help though.

 

[exceldevs]

You're welcome.
Thanks for the clarification