How to find a range of numbers?
 

I am creating a worksheet for my work. I have a chart (200 rows by 20
columns) of data (all numbers). I want to enter a number and would want the
Excel to look it up in the chart and tell me where the number is located in
relation to the title of the column and the row. Can any one help me what
formula I should use?
Thank you in advance.
DORI

How to find a range of numbers?
 

This may be a repost....my PC did something *funny*

See if this gets you pointed in the right direction:

With your table in cells B2:U201

A1: (the number you are trying to find)
A2: ="RowRef:"&SUMPRODUCT(--(B2:U201=A1)*ROW(B2:U201))&"
ColRef:"&SUMPRODUCT(--(B2:U201=A1)*COLUMN(B2:U201))

(That formula is all in one cell)

Adjust references to suit your situation.

Does that help?

***********
Regards,
Ron


"DORI" wrote:

I am creating a worksheet for my work. I have a chart (200 rows by 20
columns) of data (all numbers). I want to enter a number and would want the
Excel to look it up in the chart and tell me where the number is located in
relation to the title of the column and the row. Can any one help me what
formula I should use?
Thank you in advance.
DORI

How to find a range of numbers?
 

Thank you Ron, you are a genius.
Dori

"Ron Coderre" wrote:

This may be a repost....my PC did something *funny*

See if this gets you pointed in the right direction:

With your table in cells B2:U201

A1: (the number you are trying to find)
A2: ="RowRef:"&SUMPRODUCT(--(B2:U201=A1)*ROW(B2:U201))&"
ColRef:"&SUMPRODUCT(--(B2:U201=A1)*COLUMN(B2:U201))

(That formula is all in one cell)

Adjust references to suit your situation.

Does that help?

***********
Regards,
Ron


"DORI" wrote:

I am creating a worksheet for my work. I have a chart (200 rows by 20
columns) of data (all numbers). I want to enter a number and would want the
Excel to look it up in the chart and tell me where the number is located in
relation to the title of the column and the row. Can any one help me what
formula I should use?
Thank you in advance.
DORI

How to find a range of numbers?
 

You're very welcome. I'm just glad I could help.

***********
Regards,
Ron


"DORI" wrote:

Thank you Ron, you are a genius.
Dori

"Ron Coderre" wrote:

This may be a repost....my PC did something *funny*

See if this gets you pointed in the right direction:

With your table in cells B2:U201

A1: (the number you are trying to find)
A2: ="RowRef:"&SUMPRODUCT(--(B2:U201=A1)*ROW(B2:U201))&"
ColRef:"&SUMPRODUCT(--(B2:U201=A1)*COLUMN(B2:U201))

(That formula is all in one cell)

Adjust references to suit your situation.

Does that help?

***********
Regards,
Ron


"DORI" wrote:

I am creating a worksheet for my work. I have a chart (200 rows by 20
columns) of data (all numbers). I want to enter a number and would want the
Excel to look it up in the chart and tell me where the number is located in
relation to the title of the column and the row. Can any one help me what
formula I should use?
Thank you in advance.
DORI