Lookup/VLookup/HLookup

[JordanR]

I've been playing with all of them and I may be missing something.
The goal is, obviously, to perform lookups based on data that I have in a table.

So if I have a table

         Jan 06   Feb06  March06
Stuff      5        4       9
Things     6        5       1

Firstif I want to find out what month the most Stuff occured, I figured I'd do some kind of HLookup where the Lookup_value is a MAX() function. This doesn't seem to work. Any ideas?
Second, If I want to know what month the least Things occured I can't get it to recognize the split range. Do I have to name the range? Will that work? Is there some other way to do it?

Thanks.

 

[scott-atkinson]

Jordan,

Sounds like you need a pivot table. Highlight you table of data, open the data menu and select Pivot Table, you can now select your criteria that you want to viw in the pivot, this will include max and min data, summing data, and averaging data.

Vlookup/ Hlookup, will only return values based on on a series of criteria such as (data to match to begin lookup_range of data to look at to find the match_No. rows / columns away from the matched data to which you wish to return the value in)

For example, in you data table if you wanted to find the value for Stuff recorded against the month of March 06, you formula would look like this Vlookup(v10(supposing your word stuff is in another table),_Table name_A1:B4,4,0). The last criteria could be a '0','False','True'.

Hope this helps.

 

[shades]

Okay, use the INDEX/MATCH formula to provide what you want. This assumes that your data (including row and col headers) is in A1: D3.

=INDEX(B1: D3,MATCH($A2,$A$1:$A$3,FALSE)-1,MATCH(MAX(B2: D2),B2: D2,FALSE))

Actually, if you put the following formula (more flexible) into cell E2, then you can copy down:

=INDEX($B$1:$D$3,MATCH($A3,$A$1:$A$3,FALSE)-ROW()+1,MATCH(MAX($B3:$D3),$B3:$D3,FALSE))
________
Nevada Dispensaries

 

[shades]

The following will give you the MIN for any item in Col A. Put this in F2 and copy down.

=INDEX($B$1:$D$3,MATCH($A2,$A$1:$A$3,FALSE)-ROW()+1,MATCH(MIN($B2:$D2),$B2:$D2,FALSE))
________
MAINE MEDICAL MARIJUANA DISPENSARIES