HLOOKUP-ish?

[abba92]

I'm stumped. I know you can't do an HLOOKUP if you're not starting at
the top of the range. I have a worksheet (called "Data") that
contains 3500 rows of data. There are multiple "departments" within
this data. I want to be able to look up a specific "department" and
return the result in the row beneath it (on the worksheet "Results"),
even if the department isn't the first row. Here's an example:

From the "Data" worksheet:

1. 2008149Bob
2. 2008149Donna
3. 2008149Annalisa
4. 2008149Lindy
5. 2008153Total
6. 2008153Joe
7. 2008153Marty
8. 2008153Larry
9. 2008153Anne
10. 2008153Owen
11. 2008153William
12. 2008153Amy
13. 2008153Jim
14. 2008153Nina
15. 2008153Thad
16. 2008153Richard
17. 2008153Erica

On a different worksheet ("Results", I want to look up Department
153's Total (row 5) and return the results from the row beneath it (in
this case, "2008153Joe"). Because the departments aren't always in the
same range (sometimes Dept 153 will be in row 5, sometimes in row 116,
etc), I can't give the range a 'set' number.

Is this possible?

 

[Dave Peterson]

But you always want to return the row under the matching cell?

=index(data!a:a,match(a1,data!a:a,0)+1)

A1 of the results sheet has to contain 2008153Total, right?

 

[abba92]

But you always want to return the row under the matching cell?

=index(data!a:a,match(a1,data!a:a,0)+1)

A1 of the results sheet has to contain 2008153Total, right?










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave! You are a genius! Works great! Thank you so much!

Jim