VLOOKUP using the results of a formula as LOOKUP VALUE

[rogihead]

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 the indirect to no avail in different ways, and not sure that I fully understand the usage. So at this point I am at a standstill and Googling for a method to do this.

 

[rogihead]

I tried the copy but that just puts the data as text and you cannot see the formulae or the multiple sheets.

 

[Slizer6893]

Try the formula I posted.

 

[rogihead]

I just copied that in and that looks like it will do the trick. I need to verify the data it reported back. I did have to make one change

=INDEX(DAP!F$11:F$281,MATCH(REPLACE('RAW DATA'!A2,1,2,"")+0,DAP!A$11:A$281,0))

So when I copy it down the columns it does not change the array it is looking at.

 

[Hercules1946]

Thank you for the input Hercules1946 but that too returns an #NA

The only thing I couldn't check was that the range DAP had the look up column as the first in the range, and that the 5th column in the range contains the data you want to return.
I tested that it worked with my data modelled on what you describe in your OP. If you can post a screenshot, I'll look at it again. You did say that the name of the lookup range was DAP ?
Check in your name manager to ensure that range DAP is correctly defined as DAP!$A$3:$K$207 (where A3:K207 is the lookup range).

Hercules

 

[Slizer6893]

Does this mean you resolved it. I figured you may need to set up the references later wasn't sure how far down your data went. I hope it all works for you now.

 

[Hercules1946]

Ive just realised about the number to string comparison when you strip out "RO" from RAW DATA, causing an #N/A (fixed by the +0). My example data didn't have this problem. Ive also added your correct lookup range, and Im confident that this will work, and is quite easy to follow:

=VLOOKUP(REPLACE('RAW DATA'!E4,1,2, "")+0,DAP!$A$11:$F$281,5,FALSE)


Good Luck
Hercules

 

[rogihead]

Yes both of the solutions offered did work according ly and thank you to all.

I do have another issue that I am trying to work out and I think that I am pretty close on figuring it out, or at least I think I am.

I see where Mr. Excel offered a publication using Vlookup, and since I do use this a bit, I have purchased it. Possibly that will help with the issue that I am running into.

Thanks again top ALL who gave a hand especially Hercules1946 and Slizer6893.

 

[Hercules1946]

Your welcome. Let us know if we can help with the new problem, although if you can find your own solution, even better.

 

[Slizer6893]

Here to help, long time lurker finally made an account and decided to use my skills for others.