Question re: LOOKUP

[boz]

Hi, I hope someone can tell me how to to a lookup as follows:

I have a large sheet that contains columns of variable data (sometimes empty cells). The cols are arb. numbered 1 to nnn. I would like to have a summary column at the end that returns the header number of the column that contains the maximum value in each row (i.e. the winning bidder). LOOKUP would work save for the fact that the row must be sorted, which I am loath to do - each row would need to be sorted separately....

Any ideas?

Boz

 

[Gates Is Antichrist]

How about in the column following the end of the row - let's say that's J:

J5=Hlookup(max($A5:I5),$A5:I5,1,false)

Untested

OTTOOMH I can see an issue with "ties for the win." Maybe a countif() in K5 would signal that there was no winner. Are you with me on all this?

_________________
(edited to fix a parenthesis)
______________
Because he is.
This message was edited by Gates Is Antichrist on 2002-09-07 21:43

 

[boz]

Thanks, but this formula yields the max bid - I want the bidder who submitted that bid....

I can work out the problem with ties - just not this (seemingly) simple problem.

If I only could still use dBase.... <sigh>

Boz

 

[PaddyD]

see the simulation, which used offset & match to return the desired column heading:

=OFFSET($A$1,0,MATCH(MAX(A2:G2),A2:G2,0)-1,1,1)

No account has been taken for ties.

Book6
	A	B	C	D	E	F	G	H
1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Max
2	43	20	27	1	6	44	17	Col6
3	8	15	5	16	1	15	24	Col7
4	3	37	2	19	35	33	34	Col2
5	34	44	25	42	5	36	37	Col2
6	10	40	33	32	18	23	44	Col7
7	22	7	44	45	15	1	19	Col4
8	50	22	31	29	10	21	44	Col1
9	29	42	24	22	20	28	33	Col2
10	34	41	32	16	27	7	25	Col2
11
12
Sheet1

Paddy

 

[boz]

Thank you very much - that does the trick. I flailed around in help looking for a function listing - to no avail. You have saved me many headaches (and a lot of time)!

Boz

 

[Gates Is Antichrist]

Thank you Paddy for reading the question more carefully than I did! Mine still would have worked fine; you'd just need the name (or a copy) below the bids, and set the third argument of vlookup to suit it. (The only reason for that is that the lookups (stupidly!) forbid a negative 3rd arg.) I just happen to prefer the built-in offset in vlookup to using OFFSET, more for style than anything important.

As to paddy dropping the $ from the A, you'll be fine if you don't do something stupid, like copying [his] column H to K.

 

[boz]

Thanks again to both of you. Now, since I got *that* off my wishlist, I have been struggling with a way to do another task. I wish to take however many bids I have in each row and determine the SECOND HIGHEST. I award the high bidder the lot at 10% over the underbidder, not to exceed his maximum. I can do all the math stuff, but how to determine the second larget number in a row with varying numbers of values (bids) and no sorting?

TIA,
Boz

 

[Aladin Akyurek]

On 2002-09-08 07:23, boz wrote:
Thanks again to both of you. Now, since I got *that* off my wishlist, I have been struggling with a way to do another task. I wish to take however many bids I have in each row and determine the SECOND HIGHEST. I award the high bidder the lot at 10% over the underbidder, not to exceed his maximum. I can do all the math stuff, but how to determine the second larget number in a row with varying numbers of values (bids) and no sorting?

TIA,
Boz

=LARGE(Range,2)