Question re: LOOKUP

boz

New Member
Joined
Sep 6, 2002
Messages
4
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

New Member
Joined
Sep 6, 2002
Messages
4
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

boz

New Member
Joined
Sep 6, 2002
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

New Member
Joined
Sep 6, 2002
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

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

Which adblocker are you using?

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
Top