I’m sure this is a case of not putting the right search string into Google, but it is Monday morning….

Say I have two columns of unsorted data A1:A10 and B1:10. I want to find, say the maximum in A1:10 and (Say, A7) output this value to A11….. but then I also want to know what the value is in the cell adjacent to the maximum in column A and output this to B11 (i.e the value in B7)…… and I want to repeat the process using the maximum in the column B data as well – I think this second requirement precludes using VLOOKUP? Any suggestions – that doesn’t involve manipulating the data again?

Thanks.

Use MATCH and INDEX to recreate the vlookup functionality in column B when you are not looking at the far left of an array.

Bless you Stoner….. I shall give it a try….

Prego

Never start a spready in A1.. i always start on C6 as it gives you the opportunity to add ‘helper’ columns.

While being frowned upon by the elite, you could copy your second column in before your first and then do a vlookup.

Hide the helper afterwards if you need.

That’s handy but as with a vlookup you will only get the first match. Be careful if each entry is not unique.

Thanks all – have done it (almost neatly) with a combination of INDEX to find the row reference for max/min and INDIRECT to make up the cell reference and output the values as needed generally across all columns.

R_B – point taken, and I will have to be careful of that, but as it is test data with something like 7 significant figures, duplication in ~100 rows is pretty small.

You could add a COUNTIF formula to look for duplicates and highlight it to the user if you were concerned. 7sf may be good but what if the test is very accurate and repeatable?

789453.1
789453.4
789453.2
789453.1

Opps a duplicate!

I think this is tidier than using INDIRECT

=index($A$1:$A$10, match(max(B1:B10),B1:B10, 0))

Indirect is hard to audit too.

Oooo – very tidy – I like that.

(EDIT: I see that’s what you were trying to get me to do int he first place….. :oops:)

yep – sorry was on the phone earlier and didnt want to guess at the syntax before getting to a computer and putting the right formulae up 🙂