Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the "RO", and some have 5 digits. To do that I used

=REPLACE('RAW DATA'!A3,1,2,"")

Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match, and I need it to return that value to the cell.

I have tried using INDIRECT in different ways to no avail, and I'm not sure that I fully understand its usage. So at this point I am Googling for a method to do this and at a standstill.

share|improve this question
Please edit your question to a) not use caps lock in the title, and b) show an example of what you've tried and what hasn't worked. If you have a specific question, we're happy to help, but right now your question is too broad (e.g. "write my spreadsheet"). Include specific formula and resulting behavior. – nhinkle Oct 25 '13 at 16:26
@nhinkle I think this is reasonably clear and really not that broad at all. This is just a case of someone being confused about how to nest functions in a formula, and the solution is pretty straightforward. I'd be happy to post an answer if this is reopened. – Excellll Oct 29 '13 at 15:54
@Excellll reopened. Thanks for editing the question to make it more clear. – nhinkle Oct 29 '13 at 16:48

If, for example, your REPLACE function is in B3, you can just use


The OFFSET function makes the VLOOKUP look in the column 4 columns to the right of the first column of DAP.

This formula should return your lookup value (i.e., the value of B3) if it's found and an error if it is not. From what I can tell, this is what you wanted. If it's not, please leave a comment and let me know what you want.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .