LOCATE THE CELL WITH THE MAXIMUM VALUE

[Legacy 116183]

I have a workbook containg twelve identical worksheets.

I want to locate the cell with the maximum value: Not the value itself, I have that from this: =MAX('Prizes October 2021:Prizes September 2022'!J3:J124,1)
but the address of the cell containing it.

I have tried various combinations of MATCH, VLOOKUP etc without success.

Is there any way of doing that? The answer should be Prizes May 2022 J63.

If I can do that I'd like the information contained in K63 to be available next to the cell reference.

Is it possible?

 

[etaf]

EDIT - I missed you are wanting from lots of different worksheets

so ignore

how about using

=CELL("address",INDEX(A1:B10,MATCH(MAX(A1:A9),A1:A10,0),2))
to get the address
and
=INDEX(B1:B9,MATCH(MAX(A1:A9),A1:A9,0))
to get the value

this website may help

Get address of lookup result

To get the address of the result returned by the XLOOKUP function or the INDEX function, you can use the CELL function. In the example shown, the formula in cell F6 is: =CELL("address",XLOOKUP(F4,B5:B17,C5:C17)) The result is $C$10, the address of the cell returned by XLOOKUP. See below for an...

exceljet.net

heres a simple example using the same sheet - for an example
I'm not sure of the layout - and sheet name looks like
'Prizes October 2021:Prizes September 2022'!

Product-ETAF.xlsx
	A	B	C	D	E
1	1	a		i	$B$9
2	2	b
3	3	c
4	4	d
5	1	e
6	2	f
7	3	g
8	4	h
9	6	i
Sheet3

Cell Formulas
Range	Formula
D1	D1	=INDEX(B1:B9,MATCH(MAX(A1:A9),A1:A9,0))
E1	E1	=CELL("address",INDEX(A1:B10,MATCH(MAX(A1:A9),A1:A10,0),2))

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Legacy 116183]

I did try reading that web page, along with probably twenty fives others, but it was beyond my comprehension. Hence my posting.
I'll try and post a copy of my worksheet later on, all twelve of them are identical apart from the name, but to summarise I have Column J Row 3: J 124 each with a number is all twelve worksheets and all I want to do is identify the cell containing the highest value. Not the value itself.
The worksheets are called Prizes Oct 2021 through to Prizes Sep 2022

Thanks for you help so far.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Locate Cell Address from Multiple Worksheets
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Legacy 116183]

Here is one of the sheets with theXL2BB format

Book1
	J	K	L	M	N
1	ENTRANTS	INCOME
2		Saturday/SundayTuesday Competition	Twilight Stab	Scramble	Major Trophy
3	129	£516.00
4	6	£24.00
5	38	£152.00
6	23				£115.00
7	111	£444.00
8	19	£76.00
9	11		£27.50
10	3	£12.00
11	154	£616.00
12	44	£176.00
13	107	£428.00
14	25	£100.00
15	157				£785.00
16	80			£400.00
17	94	£376.00
18	12	£48.00
19	7		£17.50
20	154	£616.00
21	64	£256.00
22	111	£444.00
23	16	£64.00
24	5		£12.50
25	159				£795.00
26	3	£12.00
27	26	£104.00
28	76	£950.00
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sheet1

 

[Legacy 116183]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Locate Cell Address from Multiple Worksheets
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I have withdrawn from the other forum.

 

[Peter_SSs]

I have withdrawn from the other forum.

.. did you perhaps forget about this part of @Fluff's post though?

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Also, is your profile up-to-date and showing the correct office version?

 

[Legacy 116183]

My profile is still up to date. My Excel account is 2013, though I now also have access at work via Excel 365.

This workbook has all been done in Excel 13,

 

[Fluff]

You seem to have ignored this part of post#4, please do as requested.

If you have posted the question at more places, please provide links to those as well.

 

[Legacy 116183]

You seem to have ignored this part of post#4, please do as requested.

Locate Cell Address from Multiple Worksheets (Thread closed: No Answer received.)