| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Constructing range address given row and col numbers of boundaries | Excel Worksheet Functions | |||
| how to format only specific characters or numbers within each cellwithin a range of cells | Excel Discussion (Misc queries) | |||
| Find last occurance of text in range | Excel Worksheet Functions | |||
| How do you find duplicate values in excel- 2 columns of numbers | Excel Discussion (Misc queries) | |||
| Use MATCH to find position of max in 2D range? | Excel Worksheet Functions | |||
Linear Mode
