Here's another approach...

Assuming that the first and second row contain your headers, and that
your data starts on the third row...

1) Select H3:H7

2) With those cells highlighted, enter the following array formula that
needs to be confirmed with CONTROL+SHIFT+ENTER:

=IF(L2<"",TRANSPOSE(B2:F2),"")

3) Select I3:I7

4) With those cells highlighted, enter the following array formula that
needs to be confirmed with CONTROL+SHIFT+ENTER:

=IF(L2<"",TRANSPOSE(INDEX(B3:F7,MATCH(L2,A3:A7,0) ,0)),"")

5) Enter the following formula in J3 and copy down:

=IF(I3<"",RANK(I3,$I$3:$I$7)+COUNTIF($I$3:I3,I3)-1,"")

So far, this will give you your helper columns which can be hidden if
you so desire.

L2: enter the Employee ID of interest

M3, copied across:

=IF($L$2<"",INDEX($H$3:$H$7,MATCH(COLUMN()-COLUMN($M$3)+1,$J$3:$J$7,0)),
"")

M4, copied across:

=IF($L$2<"",INDEX($I$3:$I$7,MATCH(COLUMN()-COLUMN($M$4)+1,$J$3:$J$7,0)),
"")

Hope this helps!

In article . com,
wrote:

I have been working on this one for an hour or so. Any help would be
greatly appreciated.

Unfortunately, I am not good with Match and Index, most likely both of
which would need to be used in the example below.

Here is my Data (numbers under state represent units sold in each
state):

Emp ID State
. CA NY TX FL IA
1045 10 10 0 40 70
1046 20 80 40 70 90
1047 0 0 60 50 30
1048 50 50 10 20 20
1049 60 20 80 10 0

I would like to set up a formula by which a person can type in an
employee ID and retrieve all the sales data for that employee.

I envision it listing out each state and corresponding units sold for
that employee. I would like the output to be sorted by units sold
highest to lowest.

I have used a combination of Index, Match and Large functions to get
what I want for a known employee number but that doesn't work if I have
an unknown employee (prior to user input). I am assuming a lookup
would need to be nested in there somewhere. I am just having trouble
figuring out the order. Then again, Maybe it I am missing the boat
completely.

Also, if anyone has any suggestions on breaking ties in rankings using
the LARGE function, I could use some advice there also.
Thanks in advance