# IF / COUNTIF / MATCH row reference

#### ScubaTig

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### exceldevs

##### Active Member
Hi ScubaTig,

Try
Code:
``=IF(COUNTIF(INDIRECT(\$A\$2),B1)>0,IF((MATCH(B1,INDIRECT(\$A\$2),0))<=5,B1,A1),A1)``

#### ScubaTig

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

##### MrExcel MVP
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

##### MrExcel MVP

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

Code:
``````In C1

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

Markmzz

#### ScubaTig

##### New Member
That looks like it will work. THANK YOU!!!

#### exceldevs

##### Active Member

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

##### MrExcel MVP
That looks like it will work. THANK YOU!!!

You are welcome and I'm happy to help.

Markmzz

#### ScubaTig

##### New Member
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

##### Active Member
You're welcome.
Thanks for the clarification

Replies
5
Views
752
Replies
1
Views
351
Replies
1
Views
213
Replies
1
Views
284
Replies
16
Views
563

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,706
Messages
5,833,238
Members
430,198
Latest member
KitaYama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back