CODEWITHSUNDEEP
excel
user12821291
user12821291

Reputation:

Extract a letter from a cell and vlookup the value

I have a cell which contains for example: R001 but the column that has the data im searching it does not have the letter "R".

To solve this, i use the extract formula. I also tried the right formula, and works the same. Now that i have only numbers, i'm using a vlookup formula to search for the values but i have an error displaying there is a value not available for this formula or function so the cell contains "#N/A"

How i may solve this issue?

Here is the row where the first column is the data with the letter, the third column is the data without the letter.

enter image description here

Here is the column (A) from the next sheet where i need to look for the value:

enter image description here

Sorry i have the excel on spanish, so the formulas are also on spanish.

Substitute formula would be the "extract" that i'm referring to.

Upvotes: 0

Views: 659

Answers (2)

user12821291
user12821291

Reputation:

I finished using =IF(A2<>"";VALUE(TRIM(A2;2;13));"") and works!

Thanks for the fast replies.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96771

Here is a typical example that you may be able to adapt to your needs:

=VLOOKUP(--MID(A1,2,99),C1:D9,2,FALSE)

for this schema:

enter image description here

(In the example above, I converted the lookup value to a number to match the values in column C; in your case, converting to a number may not be necessary)

Upvotes: 1

Related Questions