combining the match & offset function

[Guest]

Hi, I am trying to match a cell value on one worksheet and return an value in the next column and one row up in another.

The second Sheet ('Account Info'!) is set up like this.
A B

Petty Cash-US
10050-000-00-0121-00



I have a cell returning the sheet name (A2) and the text "Account #:"

="Account # : "&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). It would read: Account #: 10050

I want to match that with data in a seperate sheet ('Account Info'!A) and return the account name which is one row up and in column B ('Account Info'!A)


I have been trying match,vlookup and offset functions but have not got this to work yet.

Can someone give me an idea how to get this done?

Thanks,


Todd

 

[Guest]

I have been working! I rearranged the data to line it up so I don't need the offset now. Here is what I have right now. The second Mid funtion does not work.

=VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",B1))+1,255),MID('Account Info'!A:A,1,5),2,FALSE)


Again, thanks for any help.