Excel 2007 : Is it possible to return an array using hlookup?

[adanglst]

Hi

I am trying to display the best performer for a certain date. This is what the table looks like:


sales: 1/2/10 1/3/10 1/4/10
dave 1 2 3
tim 9 9 9
jack 1 2 3


input: date

output: tim


I want my formula to look up on a certain date, who the best sales person is.

What I've used to return the best sales person @ 1/2 is:

=Offset(
sales,
Match( Max ( 1, 9 , 1),
[1,9,1],
0
)

However, I would like to use Hlookup to go through the dates and put in the ARRAYS for the corresponding dates where " 1, 9, 1" are supposed to be.

So if I put in 1/2 it would return the array " 2 9 2" and not the values "2 9 2" so this way I don't have to manually change the formula each time I do a search in my data table.


attached file:Excel Hlookup Help.xlsx

[martindwilson]

a workbook would be easier to look at!

[adanglst]

a workbook would be easier to look at!

Hi,

Hmm I attached a sample data set with an explanation of what I'm trying to do. I hope this helps!


Excel Hlookup Help.xlsx

[martindwilson]

try
=INDEX(A10:A15,MATCH(MAX(OFFSET(A10:A15,,MATCH(B3,B9:E9))),OFFSET(A10:A15,,MATCH(B3,B9:E9)),0))

[romperstomper]

Just to avoid OFFSET, I'd use:
=INDEX($A$10:$A$15,MATCH(MAX(INDEX($B$10:$E$15,,MATCH(B3,$B$9:$E$9,0))),INDEX($B$10:$E$15,,MATCH(B3,$B$9:$E$9,0)),0))
You could shorten it with a helper cell storing the MATCH(B3,$B$9:$E$9,0) part.

[adanglst]

thank you works perfectly!

i used index instead of offset

this was so much easier with index

will never use hlookup again lol