Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel General » Excel lookup function - harder than normal

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 01-18-2007, 01:51 PM
twogoodtwo twogoodtwo is offline
Registered User
  
Join Date: 05 Aug 2004
Posts: 18
twogoodtwo is becoming part of the community
Excel lookup function - harder than normal

Please Register to Remove these Ads

Wondering if someone could help:

I have a database with a 'MAX' function to the right of it. I am trying to lookup that max value in the row and return the title in row 1 that it corresponds to.

Failing that, I would like to return the actual column that the corresponding number is in so I can then write another formula using that colomn reference point.

Can anyone please help?

Thanks in advance!!
Reply With Quote
  #2  
Old 01-18-2007, 02:26 PM
samprince samprince is offline
Forum Contributor
  
Join Date: 05 May 2006
Posts: 142
samprince is becoming part of the community
Use Index & Match

Formula would be something along the lines of:

=INdex($A$1:$E$1,Match(MAX(A2:E2),A2:E2,0)

I think,

Basically you are telling excel to look for the max value in Range A2:E2 and return it's position.

So if the Max value was 100, and in A4, it would return the 4.

Then INdex Says return in A1:E1 what is position 4..


Sorry i cant be more specific but that is definately what you needs, I dont have excel access atm. ]=
Reply With Quote
  #3  
Old 01-18-2007, 03:24 PM
twogoodtwo twogoodtwo is offline
Registered User
  
Join Date: 05 Aug 2004
Posts: 18
twogoodtwo is becoming part of the community
Thanks
Thank you so much, I tried for ages with the INDEX function and just could not get it to work.
Reply With Quote
  #4  
Old 01-18-2007, 05:42 PM
twogoodtwo twogoodtwo is offline
Registered User
  
Join Date: 05 Aug 2004
Posts: 18
twogoodtwo is becoming part of the community
Follow up
In a data table, is there a way of search for the max number in the table and returning the corresponding title at the top of the table and name at the left of the table.

w x y z
a 1 2 6 3
b 7 8 2 3
c 4 9 4 1
d 6 2 3 7


ie, to look up the max (9 in this case) and return x in one cell and c in another??
Reply With Quote
  #5  
Old 01-18-2007, 06:09 PM
daddylonglegs's Avatar
daddylonglegs daddylonglegs is online now
Forum Moderator
  
Join Date: 14 Jan 2006
Location: England
MS Office Version:2002 (work), 2003 & 2007 (home)
Posts: 6,562
daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding
This is only a good appraoch if you can guarantee a single max value (no ties). If the numbers are in B2:E5 with column headers in E1:E5 and row "headers" in A2:A5

=INDEX(B1:E1,MIN(IF(B2:E5=MAX(B2:E5),COLUMN(B1:E1)-COLUMN(B1)+1)))

and

=INDEX(A2:A5,MIN(IF(B2:E5=MAX(B2:E5),ROW(A2:A5)-ROW(A2)+1)))

both confirmed with CTRL+SHIFT+ENTER
Reply With Quote
  #6  
Old 01-18-2007, 06:30 PM
twogoodtwo twogoodtwo is offline
Registered User
  
Join Date: 05 Aug 2004
Posts: 18
twogoodtwo is becoming part of the community
Thanks again!!
It works - that is tremendous news!!

Thanks ever so much
Reply With Quote
  #7  
Old 01-19-2007, 01:30 AM
BenjieLop BenjieLop is offline
Forum Guru
  
Join Date: 23 Jun 2004
Location: Houston, TX
Posts: 567
BenjieLop is becoming part of the community
Quote:
Originally Posted by daddylonglegs
This is only a good appraoch if you can guarantee a single max value (no ties). If the numbers are in B2:E5 with column headers in E1:E5 and row "headers" in A2:A5

=INDEX(B1:E1,MIN(IF(B2:E5=MAX(B2:E5),COLUMN(B1:E1)-COLUMN(B1)+1)))

and

=INDEX(A2:A5,MIN(IF(B2:E5=MAX(B2:E5),ROW(A2:A5)-ROW(A2)+1)))

both confirmed with CTRL+SHIFT+ENTER
I am following this with intent interest. I have a follow up question on this topic. What if the maximum number is not a unique one, i.e, for example, there are, say, 2 maximum numbers? How will the above formulas be modified?



Regards.
__________________
BenjieLop
Houston, TX
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Contact Us - ExcelTip.com - Archive - Top