excel - vlookup and offset 2

[LGMan]

Hi, I get a weekly text file which has loads of info that I'm not interested in, but a few lines which are important. The problem is....
The row numbers where the important stuff is held is not fixed.
What I'd like to do is use vlookup to find the employee reference which will be somewhere in column b and return the value that's 1 row below, as well as the value that's in the cell 1 row below and 1 column across.
So it's a mixture of Vlookup and Offset, but I can't figure out how it can be written if at all.

If it's possible, any takers, or if not is there a better way?

Thanks in advance
LGMan

 

[SkipVought]

Hi,

Use MATCH to find the row offset and then INDEX to return that row+1...
[tt]
=INDEX
(
TheWholeDataRange
,MATCH(YourLookupValue,TheLookupColumnRange,0)+1
,TheColumnOffsetOfTheDataYouWantToReturn
)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Let's say the value you're looking up in in cell F1, and you're looking for data in columns B and C.

To pull the value one row below where the match occurs:
[tab][COLOR=blue white]=Index(B:C, Match(A1, B:B,0) + 1, 1)[/color]

To pull the value one row down and one column to the right:
[tab][COLOR=blue white]=Index(B:C, Match(A1, B:B,0) + 1, 2)[/color]


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[LGMan]

Thanks both for your speedy responses again, I knew there must have been a way to achieve it, but just lack that bit of knowledge. Just wish the excel help was more help!