IF / COUNTIF / MATCH row reference

ScubaTig

New Member
Joined
Jan 28, 2014
Messages
12
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
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
Joined
Jan 28, 2014
Messages
12
I can't get that to work. Doesn't look like anything in that formula tells it to look anywhere on Sheet2???
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
May 7, 2011
Messages
3,699

ADVERTISEMENT

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
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255

ADVERTISEMENT

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.
 

ScubaTig

New Member
Joined
Jan 28, 2014
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top