# Question re: LOOKUP

#### boz

##### New Member
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

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

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
ABCDEFGH
1Col1Col2Col3Col4Col5Col6Col7Max
2432027164417Col6
381551611524Col7
4337219353334Col2
53444254253637Col2
610403332182344Col7
7227444515119Col4
850223129102144Col1
929422422202833Col2
103441321627725Col2
11
12
Sheet1

Paddy

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

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.

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

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)

Replies
10
Views
548
Replies
0
Views
345
Replies
9
Views
333
Replies
2
Views
269
Replies
6
Views
423

Threads
1,218,518
Messages
6,142,929
Members
450,456
Latest member
Exidur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back