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.

 

[Slizer6893]

An example of a few outputs would help but it sounds like Index(Match) would work really well for this, I may be wrong since I am not sure if I fully understand the questions.

Let's say you have the output in a new column you can then use that to look-up in the new table or data so something like this maybe.

=Index("array you want to look at or pull date from, Match("Value from output or put that formula in here), ("array that would hold that value to look through and match to),0))

Lets say the B column has the data you want, the A column holds the value you need to find,here is how I would do it

=index(B1:BXX,Match(Replace('Raw Data'!A3,1,2,""),A1:AXX,0))

Give me an example and I can try and better answer the ques

 

[rogihead]

So trying to follow. From RAW DATA I need to remove the RO in order to get the item number. That would be the REPLACE('RAW DATA'!A3,1,2,"" part of the equation.

Now I want to MATCH that result to the values listed in Column A cells 11 through 281 on the sheet DAP. Then the value (price) that I need returned is in listed in Column F in Cells 11 to 281.

So I came up with =INDEX(DAP!A11:A281,MATCH(REPLACE('RAW DATA'!A3,1,2,""),DAP!F11:F281,0)) but that returns an #NA

As far as an example do you mean the sheets or just a more detailed explanation?

 

[Jonmo1]

The REPLACE function returns a TEXT string, even if it's result looks like a number.
If the values you're trying to match that against are numbers, then the match won't be found.

You have to coerce the result of replace to a real number, with a +0 (or --)
Try
=INDEX(DAP!A11:A281,MATCH(REPLACE('RAW DATA'!A3,1,2,"")+0,DAP!F11:F281,0))

 

[Slizer6893]

I forgot about that thanks Jonmo1. He is right, the index(Match) works for your situation but it won't read it as a number. If you post a tidbit of data or even just an example workbook I can write the formula out for you. But I'd mess around with what he wrote and figure it out, you will learn more.

 

[Hercules1946]

Hello
Try this:
=VLOOKUP(REPLACE('RAW DATA'!A3,1,2, ""),DAP,5,FALSE)

Placed on row 3 of 'ROSSDATA' and copied down.

HTH
Herculs

 

[rogihead]

I did add the +0 to the string and that still returns an #NA.

I have pared down the data in the spreadsheet to just 4 rows, how can I add the example?

 

[rogihead]

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

 

[Slizer6893]

Just select the cells and copy i think? Or take a printscreen screenshot.

 

[Slizer6893]

I didn't read your earlier post before but I think this is the formula you would want.

=Index(DAP!F11:F281,Match(Replace('Raw Data'!A3,1,2,"")+0,DAP!A11:A281,0)

See the first array is the column or array you want to pull from, the match is telling index where in that list you want to look by outputting a number. If this doesn't work I can see if INDIRECT will work but I am not a fan of it.